x
Solved

# Selecting BOTTOM values

Posted on 2008-10-15
Medium Priority
259 Views
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

0
Question by:acsakany
• 2
• 2
• 2

LVL 13

Expert Comment

ID: 22722495
select * from tbl where [COL3] = (select max([COL3]) from tbl)
0

LVL 39

Expert Comment

ID: 22722529
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)
0

Author Comment

ID: 22722651
Wouldn't both of these solutions only return one record? I need the MAX for COL3 each time COL1 changes.
0

LVL 39

Accepted Solution

BrandonGalderisi earned 200 total points
ID: 22722666
No.  Each one will return the max for each col1.

COL1          COL2          COL3
--------         --------        --------
1                 2002           4
2                 2002           4

0

LVL 13

Expert Comment

ID: 22722740
Nope each will return the max of the column
0

Author Closing Comment

ID: 31506352
Yes you are right. Thank you very much.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.