Re: selecting max row out of many

Bhavin Shah <bhavinsh_at_aludra.usc.edu> writes:

> Hi,
>
> How do you select one row with the highest column C
> out of many possible rows in sql?
>
> Ex: Table X
>
> A B C
> --------------------
> abc 1 8
> def 1 9
> ghi 2 1
>
> SELECT * FROM X WHERE B='1' will get me the first two rows, but
> how do I filter it out using column C, like taking the row
> with the highest C? I tried using max, but I couldn't get it
> to work.
>
> Thanks in advance.

The following is one way of doing it. It does a subselect inside a select to get the maximum value of C and then the outer select finds all rows where C matches that maximum value.

SELECT * FROM X WHERE C IN (SELECT max(C) FROM X);

-- 
Prasanth Kumar
kumar1_at_home.com