homepage Welcome to WebmasterWorld Guest from 23.20.34.25
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved