Select MAX value for each ID, with date tiebreaker

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select MAX value for each ID, with date tiebreaker

Author

Topic

unCANny
Starting Member

2 Posts

Posted - 2012-05-18 : 19:57:03

So to make this more interesting I have to cast strings to integers first in order to derive the "MEASUREMENT" column. Then I have to get the MAX value of the MEASUREMENT column for each ID, However in this scenario they are likely to be tied, so in that case I need to get the row with the latest DATE.

SELECT ID, LEVEL, DATE, CAST(CASE
WHEN LEVEL = 'high' THEN 3
WHEN DETECTION = 'medium' THEN 2
WHEN DETECTION = 'low' THEN 1
END AS INT) AS MEASUREMENT
FROM TABLE

Here's an example table

ID | DATE | MEASUREMENT | LEVEL
______________________________________________________________________
1 | 10/2/2010 | 2 | medium
1 | 3/6/2011 | 2 | medium
1 | 7/4/2010 | 1 | low
2 | 3/6/2009 | 3 | high
1 | 10/2/2010 | 2 | medium
1 | 10/2/2010 | 1 | low

The Output that I desire is :

ID | DATE | MEASUREMENT | LEVEL
______________________________________________________________________
1 | 10/2/2010 | 2 | medium
2 | 3/6/2009 | 3 | high

I appreciate any help you can manage.

Thanks

unCANny
Starting Member

2 Posts

Posted - 2012-05-18 : 20:39:51

I got it!

SELECT DISTINCT ID, DATE, MEASUREMENT, LEVEL
FROM (SELECT RANK() OVER (PARTITION BY id ORDER BY MEASUREMENT desc, DATE) as rank_order, ID, DATE, MEASUREMENT, LEVEL
FROM TABLE ) a
WHERE a.rank_order = 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-22 : 13:14:42

Well done, and thank you for showing your solution so it might help others.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources