CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectdistinct
Enrique Gil
Enrique Gil

Reputation: 754

Getting Distinct Id(FK) by the latest date in SQL Server

I'm currently working on a query where I need to distinct ID based on the latest date created.

Here is my diagram

|  ID  |    ModelID     |    LocationId   |  DateCreated  |
|------+----------------+-----------------+---------------|
|   1  |       1        |        5        |   02/03/2019  |
|   2  |       2        |      null       |   01/14/2019  |
|   3  |       2        |        0        |   02/03/2019  |
|   4  |       2        |        5        |   12/30/2018  |
|   5  |       4        |        3        |   01/10/2019  |
|   6  |       3        |        5        |   02/14/2019  |
|   7  |       2        |        5        |   03/13/2019  |

So my goal is to Distinct ModelId but still show if the LocationId is null or equals to 0 then if LocationId is still the same, Get the Latest DateCreated and show it to the List

Desired output: 

    |  ID  |    ModelID     |    LocationId   |  DateCreated  |
    +------+----------------+-----------------+---------------+
    |   1  |       1        |        5        |   02/03/2019  |
    |   2  |       2        |      null       |   01/14/2019  |<-still show
    |   3  |       2        |        0        |   02/03/2019  |<-still show
    |   4  |       2        |        5        |   03/13/2019  |<-The Latest
    |   5  |       4        |        3        |   01/10/2019  |
    |   6  |       3        |        5        |   02/14/2019  |

Here is my current SQL query:

SELECT 
    a.ModelID, a.LocationId,
    (SELECT TOP 1 
         CONVERT(NVARCHAR, DateCreated, 101) 
     FROM 
         db.DeliveryDates 
     WHERE
         isDeleted = 0 
         AND DeliveryId = a.DeliveryId) AS DateCreated,
FROM 
    db.DeliveryCarInfo a
WHERE
    a.isDeleted = 0

Upvotes: 0

Views: 77

Answers (4)

Md. Mustafizur Rahman
Md. Mustafizur Rahman

Reputation: 809

Please Try with below code

DECLARE @DeliveryCarInfo TABLE (id int,modelId int,locationId int null,DateCreated date)

INSERT INTO @DeliveryCarInfo
(
    id,
    modelId,
    locationId,
    DateCreated
)
VALUES
(1  ,1 ,5,'02/03/2019'),
(2  ,2 ,null,'01/14/2019'), 
(3  ,2 ,0,'02/03/2019'),
(4  ,2 ,5,'12/30/2018'),
(5  ,4 ,3,'01/10/2019'),
(6  ,3 ,5,'02/14/2019'),
(7  ,2 ,5,'03/13/2019')

Select X.Id,X.modelId,X.locationId,X.DateCreated from (SELECT *,row_number() OVER (PARTITION by modelId,locationId ORDER BY dateCreated desc)R FROM @DeliveryCarInfo

)X
WHERE X.R=1

ORDER BY X.modelId,X.DateCreated

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use ROW_NUMBER()

SELECT ModelID, LocationId, DateCreated FROM  (
    SELECT 
        a.ModelID, a.LocationId,
        CONVERT(NVARCHAR, d.DateCreated, 101) AS DateCreated,
        ROW_NUMBER() OVER(PARTITION BY a.ModelID, a.LocationId ORDER BY d.DateCreated DESC) RN
    FROM 
        db.DeliveryCarInfo a
            LEFT JOIN db.DeliveryDates d ON d.DeliveryId = a.DeliveryId AND d.isDeleted = 0 
    WHERE
        a.isDeleted = 0
) AS T WHERE RN = 1

Upvotes: 1

Mukesh Arora
Mukesh Arora

Reputation: 1813

You can try below query. Union will help to get the desired result.

with cte
as 
(
SELECT a.ModelID 
        , a.LocationId 
        , (Select top 1 convert(nvarchar,DateCreated,101) from 
        db.DeliveryDates where isDeleted=0 and DeliveryId=a.DeliveryId) as 
         DateCreated
FROM db.DeliveryCarInfo a
WHERE a.isDeleted = 0
)
select ModelID, LocationId, DateCreated 
from cte 
where isnull(LocationId, 0) = 0

union

select ModelID, LocationId, max(DateCreated) as DateCreated
from cte
where isnull(LocationId, 0) != 0
group by ModelID, LocationId

Upvotes: 1

Hemang A
Hemang A

Reputation: 1012

Please try this.

SELECT A.* from @tbl A
INNER JOIN
(
    SELECT ModelId,Max(DateCreated) As CreateDate from @tbl Group By ModelId
) As B
    ON B.ModelId = A.ModelId
    AND A.DateCreated = B.CreateDate

 UNION 

 Select * from @tbl A
 WHERE LocationId IS NULL OR LocationId = 0

Upvotes: 1

Related Questions