CODEWITHSUNDEEP
sqlt-sqlgreatest-n-per-group
Mikee
Mikee

Reputation: 27

Selecting Top 1 for Every ID

I have the following table:

| ID | ExecOrd  |   date  |
| 1  |   1.0    | 3/4/2014|
| 1  |   2.0    | 7/7/2014|
| 1  |   3.0    | 8/8/2014|
| 2  |   1.0    | 8/4/2013|
| 2  |   2.0    |12/2/2013|
| 2  |   3.0    | 1/3/2014|
| 2  |   4.0    |         |

I need to get the date of the top ExecOrd per ID of about 8000 records, and so far I can only do it for one ID:

SELECT TOP 1 date
FROM TABLE
WHERE DATE IS NOT NULL and ID = '1'
ORDER BY ExecOrd DESC

A little help would be appreciated. I have been trying to find a similar question to mine with no success.

Upvotes: 1

Views: 1140

Answers (2)

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

;with cte as (
SELECT id,row_number() over(partition by ID order byExecOrd DESC) r 
FROM TABLE WHERE DATE IS NOT NULL ) 
select id from
cte  where r=1

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

There are several ways of doing this. A generic approach is to join the table back to itself using max():

select t.date
from yourtable t
  join (select max(execord) execord, id
        from yourtable
        group by id
        ) t2 on t.id = t2.id and t.execord = t2.execord

If you're using 2005+, I prefer to use row_number():

select date
from (
  select row_number() over (partition by id order by execord desc) rn, date
  from yourtable
) t
where rn = 1;

Note: they will give different results if ties exist.

Upvotes: 1

Related Questions