MySQL Group by a field based on the maximum value of another field IN THE GROUP (not in the table)

Question

Consider the following table:


un_id   avl_id  avl_date    avl_status
1738    6377398 2011-03-10  unavailable 
1738    6377399 2011-03-11  unavailable 
1738    6377400 2011-03-12  unavailable 
1738    6719067 2011-03-12  unavailable
1738    6719351 2011-03-12  available
1738    6377401 2011-03-13  unavailable 
1738    6377402 2011-03-14  unavailable 
1738    6377403 2011-03-15  unavailable 
1738    6377404 2011-03-16  available
1738    6719068 2011-03-16  unavailable 
1738    6719352 2011-03-16  available

Which is obtained from the following query:


SELECT 
    tbl_unit.un_id,
    tbl_availability.avl_id,
    tbl_availability.avl_date, 
    tbl_availability.avl_status
FROM
    tbl_unit
INNER JOIN 
    tbl_availability ON 
    tbl_unit.un_id = tbl_availability.un_id
WHERE
    tbl_availability.avl_active='True' AND
    tbl_unit.un_active='True' AND
    tbl_availability.avl_date >= '2011-03-10' AND
    tbl_availability.avl_date

What I want is to GROUP BY un_id so that only the avl_status having the highest avl_id is displayed. i.e:

un_id   avl_id  avl_date    avl_status
1738    6377398 2011-03-10  unavailable 
1738    6377399 2011-03-11  unavailable 
1738    6719351 2011-03-12  available
1738    6377401 2011-03-13  booked 
1738    6377402 2011-03-14  booked 
1738    6377403 2011-03-15  booked 
1738    6719352 2011-03-16  available

I have tried adding GROUP BY and HAVING clauses and various subqueries, but I have failed every time....

All help appreciated! :) - Adam.

zs2020 · Accepted Answer · 2011-03-11T15:25:08.320

0

SELECT 
    tbl_unit.un_id,
    tbl_availability.avl_id,
    tbl_availability.avl_date, 
    tbl_availability.avl_status
FROM
    tbl_unit
INNER JOIN 
    (select un_id, max(avl_id) as max_avl_id from tbl_availability group by un_id) T
    on tbl_unit.un_id = T.un_id
INNER JOIN tbl_availability ON 
    T.max_avl_id = tbl_availability.avl_id
WHERE
    tbl_availability.avl_active='True' AND
    tbl_unit.un_active='True' AND
    tbl_availability.avl_date >= '2011-03-10' AND
    tbl_availability.avl_date

edited Mar 11 '11 at 15:25

answered Mar 11 '11 at 15:12

zs2020

53,766
29
154
219

Looks good, but not quite there : #1054 - Unknown column 'T.avl_id' in 'on clause' – Adam Mar 11 '11 at 15:19
Still doesn't work, and I've also tried INNER JOIN tbl_availability ON T.max_avl_id = tbl_availability.avl_id – Adam Mar 11 '11 at 15:22
Updated to "T.max_avl_id = tbl_availability.avl_id" – zs2020 Mar 11 '11 at 15:25
OK, analysing this - your solution gives the MAX(avl_id) for the given un_id from the whole table... However, what I'm looking for is the MAX(avl_id) the un_id ONLY when the avl_date for that un_id is the same. So for any un_id on a certain date, return the avl_status of the MAX(avl_id) on that date. – Adam Mar 11 '11 at 15:35
If I change the subquery to this, and run just the subquery, it gives me a list of the max_avl_id for the each unit on each date... now how do I join that back to tbl_availability? select un_id, max(avl_id) as max_avl_id, avl_date from tbl_availability group by un_id, avl_date – Adam Mar 11 '11 at 15:40

score 0 · Answer 2 · answered Mar 11 '11 at 16:32

Try this:

SELECT
     un_id,avl_status
    FROM
    ( 
    SELECT 
        tbl_unit.un_id,
        tbl_availability.avl_id,
        tbl_availability.avl_date, 
        tbl_availability.avl_status
    FROM
        tbl_unit
    INNER JOIN 
        tbl_availability ON 
        tbl_unit.un_id = tbl_availability.un_id
    WHERE
        tbl_availability.avl_active='True' AND
        tbl_unit.un_active='True' AND
        tbl_availability.avl_date >= '2011-03-10' AND
        tbl_availability.avl_date 
    )
    GROUP BY 
     un_id,avl_status
    HAVING
     avl_id=max(avl_id)

MySQL Group by a field based on the maximum value of another field IN THE GROUP (not in the table)

2 Answers2