I am currently creating an application that stores information regarding music charts and I have run in to a little trouble with a MySQL query
I want to sort all the tracks in one table by their rating descending, limiting the result to twelve records
I then want to reorder these records by their id number ascending
I am okay on the first two parts but a little unsure about including the final part
Any ideas?
Thanks
Rob
Sorting and Limiting
Moderator: General Moderators
Do you mean to say, have all records by rating, then for any records with the same rating, order by id ascending?
Code: Select all
SELECT * FROM records ORDER BY rating DESC, id ASC LIMIT 12I want to remove a proportion of one table based on one field and then rearrange the result based on another field...Jenk wrote:Do you mean to say, have all records by rating, then for any records with the same rating, order by id ascending?
Code: Select all
SELECT * FROM records ORDER BY rating DESC, id ASC LIMIT 12
...if that's any clearer!
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
if you want to do this in a subquery, it'll looking something along these lines:
That limit bit may be wrong (i.e. google it and double check), but that should at least give you the idea
If you did mean tracks and records as two seperate things, that makes it a little more interesting as then you're dealing with a foreign key (but if this is MySQL, I doubt you were using foreign keys
)
Code: Select all
SELECT *
FROM records
WHERE pri_key IN (SELECT pri_key FROM records ORDER BY rating DESC LIMIT 12)
ORDER BY id
If you did mean tracks and records as two seperate things, that makes it a little more interesting as then you're dealing with a foreign key (but if this is MySQL, I doubt you were using foreign keys