CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
user1504606
user1504606

Reputation: 129

Getting Max date from multiple table after INNER JOIN

I have the two following tables:

table 1)
ID |  HOTEL ID | NAME 
1       100      xyz 
2       101      pqr
3       102      abc
 

table 2)
ID | BOOKING ID | DEPARTURE DATE | AMOUNT
1         1       2013-04-12        100
2         1       2013-04-14        120
3         1       2013-04-9          90
4         2       2013-04-14        100
5         2       2013-04-18        150
6         3       2013-04-12        100

I want to get the following result in MySQL such that it takes the row from Table 2 with MAX DEPARTURE DATE:

ID | BOOKING ID | DEPARTURE DATE | AMOUNT
2         1       2013-04-14        120
5         2       2013-04-18        150
6         3       2013-04-12        100

Upvotes: 2

Views: 24260

Answers (2)

John Woo
John Woo

Reputation: 263693

SELECT  b.ID,
        b.BookingID,
        a.Name,
        b.departureDate,
        b.Amount
FROM    Table1 a
        INNER JOIN Table2 b
            ON a.ID = b.BookingID
        INNER JOIN
        (
            SELECT  BookingID, MAX(DepartureDate) Max_Date
            FROM    Table2
            GROUP   BY BookingID
        ) c ON  b.BookingID = c.BookingID AND
                b.DepartureDate = c.Max_date

Upvotes: 13

Yaroslav
Yaroslav

Reputation: 681

Well, 

SELECT * FROM `table2` ORDER BY `DEPARTURE_DATE` DESC LIMIT 0,1

should help

Upvotes: -4

Related Questions