MySQL Forums
Forum List  »  General

Re: Optimising a GROUP BY and ORDER BY in a joined query
Posted by: Michael Ditum
Date: April 01, 2010 11:17AM

Hi,

That query didn't execute, mysql complained: "Unknown column 'p.VP_AssetId' in 'on clause'". I tried changing it to...

SELECT p.VP_Family
   FROM (
      SELECT VP_AssetId, VP_Family
         FROM vod_package
         ORDER BY VP_Family
         LIMIT 110    -- note: a few extra
        ) p
   JOIN vod_control c  ON c.VC_AssetId = p.VP_AssetId 
   WHERE c.VC_Valid = 1 
   ORDER BY p.VP_Family
   LIMIT 100;

but got each Family multiple times. Is this what you intended?

This is one of many queries I'm doing on the data. They are all having similar problems so I tried to find the simplest one for an example.

The reason for the LIMIT is indeed for doing pagination, if you could link me to your rants I would appreciate reading them.

Thanks for your help,

Mike

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.