acsakany
asked on
Selecting BOTTOM values
I have the following data in a table
COL1 COL2 COL3
-------- -------- --------
1 2000 2
1 2001 1
1 2002 4
1 2003 3
2 2000 1
2 2001 2
2 2002 4
2 2003 3
I need to select the records that had the maximum values of COL3 for each COL1
In other words I need to select the records with the values 1,2002,4 AND 2,2002,4
How would I go about this?
COL1 COL2 COL3
-------- -------- --------
1 2000 2
1 2001 1
1 2002 4
1 2003 3
2 2000 1
2 2001 2
2 2002 4
2 2003 3
I need to select the records that had the maximum values of COL3 for each COL1
In other words I need to select the records with the values 1,2002,4 AND 2,2002,4
How would I go about this?
select * from tbl where [COL3] = (select max([COL3]) from tbl)
select a.* from YourTable a
join (select col1, max(col3) mcol3 from YourTable) b
on a.col1=b.col1
and a.col2=b.col2
or
select * from yourtable a
where col3 = (select max(col3) from yourtable where col1 = a.col1)
join (select col1, max(col3) mcol3 from YourTable) b
on a.col1=b.col1
and a.col2=b.col2
or
select * from yourtable a
where col3 = (select max(col3) from yourtable where col1 = a.col1)
ASKER
Wouldn't both of these solutions only return one record? I need the MAX for COL3 each time COL1 changes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nope each will return the max of the column
ASKER
Yes you are right. Thank you very much.