GROUP BY PROBLEM 2

[skezza]

Hey Guys,
Great looking forum, hope you can help me with this. I am trying to do a GROUP BY, but not having much luck.

SELECT `tblAttraction`.`AttractionName`, max(`tblAttraction`.`AttractionCapacity`), `tblAttraction`.`AttractionMinAge`, `tblThemePark`.`ParkName`
FROM `tblThemePark`
LEFT JOIN `tblAttraction` ON `tblThemePark`.`ParkID` = `tblAttraction`.`ParkID`
WHERE (`tblAttraction`.`AttractionName` <11)
GROUP BY `tblThemePark`.`ParkName`

Basically, I want the GROUP BY to say which is the most popular ride at each theme park. I thought this was the way, any idea where i'm going wrong?

Cheers

 

[r937]

here's your query, reworked slightly:

SELECT a.AttractionName
     , a.AttractionCapacity
     , a.AttractionMinAge
     , p.ParkName
  FROM tblThemePark AS p
INNER
  JOIN ( SELECT ParkID
              , MAX(AttractionCapacity) AS max_cap
           FROM tblAttraction 
          WHERE AttractionName < 11
         GROUP
             BY ParkID ) AS m 
    ON m.ParkID = p.ParkID 
INNER
  JOIN tblAttraction AS a
    ON a.ParkID = p.ParkID 
   AND a.AttractionCapacity = m.max_cap
   AND a.AttractionName < 11
GROUP 
    BY p.ParkName

note that "max capacity" is probably not an indicator of popularity



r937.com | rudy.ca

 

[JarlH]

I'd solve it like this:
[tt]
SELECT A.AttractionName,
A.AttractionCapacity,
A.AttractionMinAge,
TP.ParkName
FROM tblThemePark as TP
JOIN tblAttraction as A ON TP.ParkID = A.ParkID
WHERE A.AttractionName < 11
AND A.AttractionCapacity = (SELECT MAX(AttractionCapacity)
FROM tblAttraction
WHERE ParkID = TP.ParkID)[/tt]

Will list all top attractions, even if there's a tie.

Core SQL-99 compliant.