Hi,

How can I select the (entire) row which contains the MAX column value?

The following query selcts the MAX of Col1 for each value of Col2 but it returns the value of Col3 for the first row of the table.

SELECT MAX(Col1), Col2, Col3 from MyTable
GROUP BY Col2

What I need is the MAX of Col1 for each value of Col2 as well as the value of Col3 in the row where MAX(Col1) occurs.

Any suggestions?
Thanks, Tony.

"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:D7CDB367-F8C1-4330-94AE-3954E6E80524@news.elevatesoft.com...
> Hi,
>
> How can I select the (entire) row which contains the MAX column value?
>
> The following query selcts the MAX of Col1 for each value of Col2 but it
> returns the value of Col3 for the first row of the table.
>
> SELECT MAX(Col1), Col2, Col3 from MyTable
> GROUP BY Col2
>
> What I need is the MAX of Col1 for each value of Col2 as well as the value
> of Col3 in the row where MAX(Col1) occurs.
>

SELECT MAX(Col1) MyMAX, Col2 MyCol2 INTO MEMORY\TEMP FROM MyTable GROUP BY
MyCol2;
SELECT [DISTINCT] MyMax, MyCol2, Col3 FROM MEMORY\TEMP
JOIN MYTABLE ON ((MyMax = Col1) and (MyCol2 = Col2));

I think so, anyway. Use the distinct if you have multiple matches on max. If
the output of the first select is large, add an index to the memory table.

Robert