SQL aggregates question

[JetDeveloper]

Say I have a collection of data sets in a table, where each data set contains the X and Y coordinates of some points.

Now for each data set, I would like to find the maximum Y-coordinate and the
X-coordinate at which this maximum occurs.


SELECT SET_ID, MAX(Y)
FROM MyTable
GROUP BY SET_ID;

This will find the maximum. Easy enough, but I also want the X where the max occured. Is there any way that this query can be modified to include that.

It would be nice if SQL allowed you to say

SELECT SET_ID, MAX(Y), X WHERE Y=MAX(Y)
FROM MyTable
GROUP BY SET_ID;

but sadly it doesn't.

Is there any easy way to do what I am talking about or do I have to do some
joins and other complicated stuff.


Thanks in advance

[erickwidya]

SELECT TOP 1 Max([Y]) AS MaxOfY, SET_ID, [X]
FROM Table1
GROUP BY SET_ID, [X]

[JetDeveloper]

That does not work. It only returns one record and it's not the max. I want the max y for each set id and the x where each one occurred.

I'm afraid I'll need to use good old-fashioned for loops from C# instead of a single SQL query.

My table

ID X Y
------------------
ABC 3 6
ABC 4 8
ABC 6 5
ABC 7 8
DEF 8 2
DEF 6 9
DEF 7 3
DEF 9 10
GHI 10 15
GHI 58 10
GHI 4 12


Should give


ID MAX(Y) X WHERE MAX OCCURED
-----------------------------------------------------
ABC 8 4
DEF 10 9
GHI 15 10


Not even this will work, but it's a little closer


SELECT ID, MAX(X) AS [X Of Y_Max], MAX(Y) AS Y_Max
FROM Table1
WHERE Y IN
(
SELECT Max(Y) AS MaxOfY
FROM Table1
GROUP BY Table1.ID
)
GROUP BY ID;


The result is


ID X Of Y_Max Y_Max
-----------------------------------
ABC 7 8
DEF 9 10
GHI 58 15


Which is wrong!!

[hspc]

Hi
this seems to be a bad performing query
but it works !!!


Select max(y) as MAXY, (Select top 1 x from table1 where id=t.id and y=(Select max(Y) from table1 where id=T.id)) as X
from Table1 as T
group by (id)
results :
MAXY X
8 4
10 9
15 10

[erickwidya]

SELECT [PK], [Xx] AS [X Of Y_Max], MAX(Yy) AS Y_Max
FROM [TblXxOfMaxYy]
WHERE [Yy] IN
(
SELECT Max([Yy]) AS MaxOfY
FROM [TblXxOfMaxYy]
GROUP BY TblXxOfMaxYy.PK
)
GROUP BY [PK], [Xx]


PS : change the field name that match yours

EDIT : ID is RESERVED Words..so use [] bracket

[srikanthvja]

hi,

can u check this and make confirm that it is not working (:0

SELECT *
FROM MyTable t1
WHERE (y IN
(SELECT MAX(y)
FROM MyTable t2
WHERE t1.id = t2.id))

let me know..all the best

[JetDeveloper]

None of these queries work so far for me in Access.

For loops and currentMax=-Infinity, if (y > currentMax) currentMax = y ... algortihm, here we go.

Oh the limitations of SQL!

[JetDeveloper]

Hmmm! This apparently works.
Ugly though and very inefficient!

SELECT T1.SET_ID, MAX(T1.X) AS [X where max occurs], MAX(T1.Y) AS MaxOfY
FROM Table1 AS T1
WHERE T1.Y IN
(SELECT MAX(T2.Y)
FROM Table1 As T2
WHERE T1.SET_ID=T2.SET_ID
GROUP BY T2.SET_ID
)
GROUP BY T1.SET_ID;

In fact it is taking minutes to complete on my table with 100000 records.

[hspc]

Hi
Did you try srikanthvja's Solution?
It seems good and will perform better i hope

ID is RESERVED Words..so use [] bracket
which database do you use ?
id works in MS Access XP and MS SQL Server 2000

[erickwidya]

hspc
i use MS Access 2000.

[JetDeveloper]

hi,

can u check this and make confirm that it is not working (:0

SELECT *
FROM MyTable t1
WHERE (y IN
(SELECT MAX(y)
FROM MyTable t2
WHERE t1.id = t2.id))

let me know..all the best

Strange ... wasn't working this morning when I tried, but now it does.
I must have miscopied it, but I did copy and paste.

Thank you srikanthvja

[JetDeveloper]

SELECT *
FROM MyTable t1
WHERE (y IN
(SELECT MAX(y)
FROM MyTable t2
WHERE t1.id = t2.id))


Still not working in Access 2003! Could Access have a bug? Very weird!

It works perfectly in Access 2000, and I'll be testing it soon with MySQL.

[JetDeveloper]

Oops! It works in both versions of Access! My tables for the two databases had different ID columns. One was the primary key and one wasn't.

My mistake! Needed to use SET_ID!!!

Thanks again!

[cjard]

SELECT SET_ID, Y, X
FROM MyTable
GROUP BY SET_ID
ORDER BY Y DESC, X DESC;

now take only the top row from the query. how you do this changes on your DB vendor. in oracle it is
SELECT * WHERE rownum < 2

in SQL SERVER
SELECT * ... LIMIT 1

in access SELECT TOP 1 FROM ...

[Krzemo]

in SQL SERVER
SELECT * ... LIMIT 1 In SQL Server is like in access :
SELECT TOP 1 * FROM ...
Best regards,
Krzemo.