CODEWITHSUNDEEP
sqlsql-serverdatabase
Stfvns
Stfvns

Reputation: 1041

Select Top 1 based on distinct columns

I need to select the top 1 record from each group of column UnitID and CompanyCode and using order by from column CreatedDate

Here's an example of my table 

ID | UnitID | CompanyCode | CreatedDate |
----------------------------------------|
1  |   A1   |    G100     | 2020-03-12  |
2  |   A1   |    G100     | 2020-03-13  |
3  |   A1   |    G100     | 2020-03-14  |
4  |   B2   |    G100     | 2020-03-12  |
5  |   B2   |    F200     | 2020-03-13  |
6  |   B2   |    E300     | 2020-03-14  |

My expected results would be these rows

ID | UnitID | CompanyCode | CreatedDate |
----------------------------------------|
3  |   A1   |    G100     | 2020-03-14  |
4  |   B2   |    G100     | 2020-03-12  |
5  |   B2   |    F200     | 2020-03-13  |
6  |   B2   |    E300     | 2020-03-14  |

We looking at UnitID first, next check CompanyCode If there is a record with different CompanyCode it will be display, but if have same, it will be select top 1 with order by createdDate

SIMPLE QUERY: SELECT ID, UnitID, CompanyCode, CreatedDate FROM Tbl_Unit ORDER BY CreatedDate

Anyone know how this can be achieved?

Upvotes: 0

Views: 52

Answers (3)

Digvijay S
Digvijay S

Reputation: 2715

Using ROW_NUMBER() Function. 

 SELECT ID  , UnitID ,  CompanyCode ,  CreatedDate FROM 
 (
 select TAB.* , ROW_NUMBER() OVER (PARTITION BY UnitId , CompanyCode order by  createddate desc )  RNK from TAB
  ) Drived WHERE RNK=1;

Demo

ROW_NUMBER() -- Funtion to generate row number
 OVER (PARTITION BY UnitId , CompanyCode  --  Partition range 
order by  createddate desc -- Sorting order )

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

I like using TOP 1 WITH TIES for handling this type of query on SQL Server:

SELECT TOP 1 WITH TIES *
FROM Tbl_Unit
ORDER BY
    ROW_NUMBER() OVER (PARTITION BY UnitID, CompanyCode ORDER BY CreatedDate DESC);

Upvotes: 2

Zhorov
Zhorov

Reputation: 30003

If I understand correctly, the next statement may help:

SELECT ID, UnitID, CompanyCode, CreatedDate
FROM (
   SELECT 
      ID, UnitID, CompanyCode, CreatedDate,
      ROW_NUMBER() OVER (PARTITION BY UnitID, CompanyCode ORDER BY CreatedDate) AS Rn
   FROM Tbl_Unit
) t
WHERE Rn = 1

Upvotes: 2

Related Questions