| Author |
 Topic  |
|
|
unCANny
Starting Member
USA
2 Posts |
 Posted - 05/18/2012 : 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
USA
2 Posts |
Posted - 05/18/2012 : 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
USA
7423 Posts |
|
| |
Topic |
|
|
|
Select MAX value for each ID, with date tiebreaker
