CODEWITHSUNDEEP
sqlsql-server
Hung Captain
Hung Captain

Reputation: 97

How to query latest records in SQL Server

I've tried to to use similar asks but still not able to get my desired result. Here is three sample records.

create table #temps
(
    airport varchar(10),
    country varchar(10),
    plane varchar(10),
    id int,
    flight_date datetime
)

insert into #temps 
values ('IAD', 'USA', 'a777', '195', '  7/26/2022 11:39:00 AM')

insert into #temps 
values ('IAD', 'USA', 'a777', '195', '  8/12/2022 9:51:00 AM')

insert into #temps 
values ('BOS', 'USA', 'a777', '195', '  8/12/2022 9:51:00 AM')

I tried to retrieve the latest record which is from BOS airport (discard the impossible of the same flight dates occurred from different airports)

  1. I used the ROW_NUMBER such as below and and wanted to return the max rank = 3.

     SELECT DISTINCT
     a.airport, a.country, a.flight_date, a.plane, id,
     ROW_NUMBER() OVER (PARTITION BY id ORDER BY flight_date ASC) AS Ct
 FROM
     #temps a

  2. I also tried the max such as

     SELECT A.airport, A.id, A.flight_date, A.country
 FROM #temps A
 INNER JOIN (SELECT id, MAX(flight_date) as MAX_FLIGHT_DATE 
             FROM #temps 
             GROUP BY id) B ON (A.flight_date = B.MAX_FLIGHT_DATE)

Is there a better technique that can return the record from BOS airport?

Thanks! joe

Upvotes: 0

Views: 48

Answers (1)

odinwithtomtom
odinwithtomtom

Reputation: 11

You could use ORDER by.

SELECT TOP(3) airport,
              country,
              plane,
              id,
              flight_date
FROM #temps
ORDER BY flight_date DESC;

Upvotes: 1

Related Questions