CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate
meder omuraliev
meder omuraliev

Reputation: 186552

How to ORDER BY/MAX before GROUP BY after a LEFT JOIN to a many table?

There are 3 tables in question - properties, specials, and properties_specials.

I want n results back where n is the number of properties, and the corresponding special is the one with the MAX(specials.startdate).

I first tried doing this after aggregation but the MIN/MAX doesn't seem to affect the result set at all:

SELECT

p.id,
s.*,
MAX( s.startdate )

FROM
properties p

LEFT JOIN properties_specials ps ON ps.properties_id = p.id
LEFT JOIN specials s ON s.id = ps.specials_id

GROUP BY p.id

Using a subquery with max doesn't work because it just grabs the total max:

SELECT

p.id,
s.*

FROM
properties p

LEFT JOIN properties_specials ps ON ps.properties_id = p.id
LEFT JOIN (
    SELECT id, MAX( specials.startdate )
    FROM specials
) AS s ON s.id = ps.specials_id

GROUP BY p.id

And finally doing an ORDER BY in the subquery doesn't seem to work either because even though I specify ORDER BY specials.startdate DESC or ORDER BY specials.startdate ASC, the result is the same for:

properties table
id  name
----------------
11  Hotel



properties_specials table
properties_id specials_id
----------------
11 33
11 34


specials table
id    startdate
----------------
33    2016-01-02
34    2016-01-10

How can I adjust this properly so I get the most recent / max after the join to properties_specials?

EDIT: Came up with a sqlfiddle for this. I think the differing factor may have been that the same special can be applied to different properties in the mapping table - if that's the case sorry for not specifying earlier.

Upvotes: 1

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this by using an aggregation to get the maxdate per property and then joining these back in:

select p.*, s.*      
from properties p join
     (select ps.properties_id, max(s.startdate) as maxsd
      from properties_specials ps join
           specials s
           on s.id = ps.specials_id
      group by ps.properties_id
     ) maxps
     on p.id = maxps.properties_id join
     properties_specials ps
     on ps.properties_id = p.id join
     specials s
     on ps.specials_id = s.id and s.startdate = maxps.maxsd;

EDIT:

Note the above query had an error. It was missing an on clause, which resulted in many duplicates (and would have been an error in any database other than MySQL).

Another approach is to just use the id instead of the date. Plugging directly into the above query:

select p.*, s.*      
from properties p join
     (select ps.properties_id, max(s.id) as maxid
      from properties_specials ps join
           specials s
           on s.id = ps.specials_id
      group by ps.properties_id
     ) maxps
     on p.id = maxps.properties_id join
     properties_specials ps 
     on ps.properties_id = maxps.properties_id join
     specials s
     on s.id = maxps.maxid;

Upvotes: 1

SIDU
SIDU

Reputation: 2278

Here is the quick answer:

SELECT ps.properties_id, max(s.startdate)
FROM specials s, properties_specials ps
WHERE s.id = ps.specials_id
GROUP BY 1

If you want all the information, try the following:

SELECT p.id pid, p.name, s.id sid, s.startdate
FROM properties p, specials s, properties_specials ps, (
    SELECT ps.properties_id pid, max(s.startdate) maxdate
    FROM specials s, properties_specials ps
    WHERE s.id = ps.specials_id
    GROUP BY 1
) as pmax
WHERE p.id = ps.properties_id AND s.id = ps.specials_id
AND p.id = pmax.pid AND pmax.maxdate = s.startdate

Upvotes: 1

Related Questions