CODEWITHSUNDEEP
sqlsql-server
Saad Khattak
Saad Khattak

Reputation: 68

Selecting unique records after order by clause

I am maintaining history of operations on vehicles in a table and now I want to get few fields of the latest operation on each of the vehicle.

This is what I tried 

SELECT HVeh.RefVehicleModel , HVeh.RefVehStatus , HVeh.ChangedDate
FROM HVeh
WHERE ChangedDate >= @X AND ChangedDate <= @Y
order by HVeh.RefVehicleModel, HVeh.ChangedDate DESC

and it returns

The above query returns me something as given below

X   1   2019-09-04 08:21:39.223
X   1   2019-09-03 13:47:48.943
X   1   2019-09-03 11:27:43.813
X   1   2019-09-03 10:57:12.080

however, I want to select only the top row now as it is the most latest entry and is of particular interest.

NOTE : If I use SELECT TOP 1, it only fetches top row of the whole returned table, however I have multiple entries(for each vehicle) as I said, I need "top" entry for each of them

Upvotes: 0

Views: 79

Answers (3)

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

SELECT HVeh.RefVehicleModel , HVeh.RefVehStatus , HVeh.ChangedDate
FROM HVeh
WHERE 
convert(DATETIME,ChangedDate)   
BETWEEN Convert(DATETIME,CASE WHEN isnull(@X,'')='' THEN ChangedDate ELSE isnull(@X,'') END)                     
AND Convert(DATETIME, CASE WHEN isnull(@Y,'')='' THEN ChangedDate ELSE isnull(@Y,'') END)  
order by HVeh.RefVehicleModel, CONVERT(DATETIME,HVeh.ChangedDate,106)  DESC

I think this query will perfect for you....

Note:- @X as FromDate and @Y as ToDate

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522787

Is this what you want?

SELECT TOP 1 WITH TIES HVeh.RefVehicleModel, HVeh.RefVehStatus, HVeh.ChangedDate
FROM HVeh
WHERE ChangedDate >= @X AND ChangedDate <= @Y
ORDER BY ROW_NUMBER() OVER (PARTITION BY HVeh.RefVehicleModel ORDER BY HVeh.ChangedDate DESC);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271171

If you want the latest record for each vehicle model, then use a correlated subquery or window functions:

SELECT RefVehicleModel, RefVehStatus, ChangedDate
FROM (SELECT HVeh.RefVehicleModel, HVeh.RefVehStatus, HVeh.ChangedDate,
             ROW_NUMBER() OVER (PARTITION BY HVeh.RefVehicleModel ORDER BY HVeh.ChangedDate DESC) as seqnum
      FROM HVeh
      WHERE ChangedDate >= @X AND ChangedDate <= @Y
     ) h
WHERE seqnum = 1;

If you want only one record, then SELECT TOP (1) is a good solution.

Upvotes: 1

Related Questions