Page 1 of 1
Sorting and Limiting
Posted: Fri Jul 13, 2007 6:57 am
by purinkle
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
Posted: Fri Jul 13, 2007 7:16 am
by feyd
Unless you're performing a subquery, I believe you'll need to do this in PHP.
Posted: Fri Jul 13, 2007 7:27 am
by Jenk
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
Posted: Fri Jul 13, 2007 8:04 am
by purinkle
feyd wrote:Unless you're performing a subquery, I believe you'll need to do this in PHP.
Excuse my ignorance but what do you mean by "subquery"?
How would I go about doing this in PHP? Should I post in the correct forum?
Thanks
Rob
Posted: Fri Jul 13, 2007 8:06 am
by purinkle
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
I want to remove a proportion of one table based on one field and then rearrange the result based on another field...
...if that's any clearer!
Posted: Fri Jul 13, 2007 10:20 am
by Jenk
then just add a where clause for field != value, or field not in (val1, val2, val3, etc)
or if they are different tables, you can outer join them.
Posted: Fri Jul 13, 2007 10:32 am
by ReverendDexter
if you want to do this in a subquery, it'll looking something along these lines:
Code: Select all
SELECT *
FROM records
WHERE pri_key IN (SELECT pri_key FROM records ORDER BY rating DESC LIMIT 12)
ORDER BY id
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

)