| 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
|
|
|
