SQL return first result for each ID

Question

My Query

Select ID, Date, Type, TypeID, Active from TableName

Results

ID      | Date                    | Type   | TypeID | Active 
------  | ----------------------- | ------ | ------ | ------
16026690| 2017-08-02 20:35:51.000 | Banana | 121    | 0
------  | ----------------------- | ------ | ------ | ------
16026695| 2017-08-02 20:43:03.000 | Banana | 121    | 0
------  | ----------------------- | ------ | ------ | ------
16026700| 2017-08-02 20:35:51.000 | Orange | 125    | 0
------  | ----------------------- | ------ | ------ | ------
16026705| 2017-08-02 20:43:03.000 | Orange | 125    | 0
------  | ----------------------- | ------ | ------ | ------
16026706| 2017-08-02 20:47:51.000 | Orange | 128    | 0
------  | ----------------------- | ------ | ------ | ------
16026706| 2017-08-02 20:50:51.000 | Orange | 128    | 0
------  | ----------------------- | ------ | ------ | ------
16026777| 2017-08-02 20:58:03.000 | Orange | 128    | 0

I want to return the most recent record for each Type/TypeID

So the results I want to see for the above table is:

ID      | Date                    | Type   | TypeID | Active 
------  | ----------------------- | ------ | ------ | ------
16026695| 2017-08-02 20:43:03.000 | Banana | 121    | 0
------  | ----------------------- | ------ | ------ | ------
16026705| 2017-08-02 20:43:03.000 | Orange | 125    | 0
------  | ----------------------- | ------ | ------ | ------
16026777| 2017-08-02 20:58:03.000 | Orange | 128    | 0

Can someone point me in the right direction to achieve this? I am using MS SQL 2016

Depends on what database type you are using. For MySQL you would `order by Date` then `limit 1`. — yanman1234, Aug 09 '17 at 15:07
Have a look here: https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Pawel Czapski, Aug 09 '17 at 15:29

domgreen · Accepted Answer · 2017-08-09T16:20:01.287

4

As mentioned in PawelCz comment the link shows you that it can be done using the following:

;WITH temp AS (
    SELECT 
        *, 
        ROW_NUMBER() OVER( PARTITION BY [TypeID] ORDER BY [Date] DESC) AS rownum 
    FROM TableName 
)

SELECT
    *
FROM
    temp
WHERE 
    rownum = 1

edited Aug 09 '17 at 16:20

answered Aug 09 '17 at 15:41

domgreen

1,358
2
8
9

Based on the edit made by OP you need to change from [Type] to [TypeID] – Pawel Czapski Aug 09 '17 at 15:54

score 2 · Answer 2 · answered Aug 09 '17 at 15:59

2

You can query as below:

Select top (1) with ties * from yourtable
   Order by Row_Number() over(Partition by [TypeId] order by [Date] desc)

answered Aug 09 '17 at 15:59

Kannan Kandasamy

13,405
3
25
38

I've never seen this usage before. How does it compare to domgreen's answer (that's the way I do this) in terms of efficiency? – KeithL Aug 09 '17 at 16:07
This is other way just to avoid subquery but still avoiding subquery will result in extra sort operator. – Kannan Kandasamy Aug 09 '17 at 16:12

score 0 · Answer 3 · answered Aug 09 '17 at 15:09

0

Select ID, Date, Type, TypeID, Active from TableName ORDER BY Date Limit 1

If you're using MySql this should work fine , if you use SQL Server , I suggest TOP 1 or 'ROW_NUMBERS' instead of LIMIT 1

answered Aug 09 '17 at 15:09

Khaled Ouertani

316
2
13

I am using SQL Server, I cannot use top 1, as there are many other results that come back, and I want to select the most recent for each type ID by Date, does this make more sense? – Jeffrey Aug 09 '17 at 15:16

SQL return first result for each ID

3 Answers3