Welcome to WebmasterWorld Guest from 54.226.32.234

Forum Moderators: open

Message Too Old, No Replies

Using SQL GROUP BY and ORDER BY

     

optik

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

5+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month