0

I am currently working on a database with the following structure:

  • Passport Number
  • Flight Number
  • Flight Date
  • Memo

Supposedly the composite key is Passport Number, Flight Number and Flight Date, I was wondering if there's anyway where I can display only the latest flight for each passport number?

My aim is to only use SQL and not stored procedure (which will otherwise be quite easy) so that the SQL is portable across databases.

Appreciate any advice please.

user1275515
  • 101
  • 1
  • There's a lot of info here that should help you -> http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – PulseLab Jun 17 '14 at 08:31

1 Answers1

0

May be you can try below query:

SELECT A.PASSPORT_NUMBER, A.FLIGHT_NUMBER, A.MAX_FLIGHT_DATE, A.MEMO
FROM PASSPORT_DB A
INNER JOIN (SELECT FLIGHT_NUMBER, MAX(FLIGHT_DATE) MAX_FLIGHT_DATE 
            FROM PASSPORT_NUMBER GROUP BY FLIGHT_NUMBER) B
  ON (A.FLIGHT_DATE= B.MAX_FLIGHT_DATE )
ngrashia
  • 9,869
  • 5
  • 43
  • 58