Hi all,

I have these tables:
1 bookings
2 bookingsFlightLists
3 flightLists

bookingsFlightLists is a table with only the bookingID and FlightListID.
Like this:
BookingID FlgihtListID
1 1
2 1
3 1
4 2
4 2
5 3
5 26

In the flightLists table I have a flightDate and flightNumber
The combnation of the flightDate and flightNumber is unique.

I need to query for the firstFlight date per booking.
I manage that with MIN(flightDate) BUT

When I want to select the flightNumber or BookingID with it I get multiple
records.
e.g.
I get:
Date :6-9-2008
Number: IR764
BookingID: 5

And this one:
Date :26-8-2008
Number: IR768
BookingID: 5

How can I make the query so that per Number and per booking the first date
is selected???

Many thanks in advance,

To get accurate answers it is always best to post table structures,
sample data, and expected results.

Try this:

SELECT bookingID, flightDate, flightNumber
FROM bookingsFlightLists AS B
LEFT JOIN (SELECT FlightListID, flightDate, flightNumber,
ROW_NUMBER() OVER(PARTITION BY FlightListID
ORDER BY flightDate) AS seq
FROM flightLists) AS L
ON B.FlightListID = L.FlightListID
AND L.seq = 1;

--
Plamen Ratchev
http://www.SQLStudio.com