Sorting and Limiting

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
purinkle
Forum Newbie
Posts: 12
Joined: Fri Jul 13, 2007 6:54 am

Sorting and Limiting

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Unless you're performing a subquery, I believe you'll need to do this in PHP.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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
purinkle
Forum Newbie
Posts: 12
Joined: Fri Jul 13, 2007 6:54 am

Post 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
purinkle
Forum Newbie
Posts: 12
Joined: Fri Jul 13, 2007 6:54 am

Post 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!
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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 ;) )
Post Reply