homepage Welcome to WebmasterWorld Guest from 107.20.25.215
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Using SQL GROUP BY and ORDER BY
optik




msg:4428194
 5:08 pm on Mar 12, 2012 (gmt 0)

I have the following statement

SELECT * FROM cds
WHERE genre='rock'
GROUP BY cat_no
ORDER BY ABS(track) DESC
LIMIT 0,3

Each CD has a row in the db for each track and another entry for the cd as a whole which instead of a track number is called 'bundle' (but only if the CD has more than one track) the above statement will select three of the 'bundle' entries but I want to be able to select the latest 3. If I use ABS(date,track) it doesn't work.

Table

genre cat_no track date
rock 001 1 09323123
rock 001 2 09323123
rock 001 bundle 09323123
rock 002 1 09323123
rock 002 2 09323123
rock 002 bundle 09323123
rock 003 1 09323123
rock 003 2 09323123
rock 003 bundle 09323123

 

Dijkgraaf




msg:4428338
 11:36 pm on Mar 12, 2012 (gmt 0)

How about
SELECT * FROM cds
WHERE genre='rock'
GROUP BY cat_no
ORDER BY date DESC, ABS(track) DESC
LIMIT 0,3

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved