[SOLVED] Retrieving and sorting data from MySQL

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

[SOLVED] Retrieving and sorting data from MySQL

Post by purinkle »

I've already posted about sorting and limiting the data I am retrieving from a database and I have been pointed in this general direction

I have a database table that looks a bit like this:

Code: Select all

+--------+-----------+-----------------------------------+------------+-----------+
| id_trc | idart_trc | name_trc                          | rating_trc | weeks_trc |
+--------+-----------+-----------------------------------+------------+-----------+
| 1      | 1         | Take Me Out                       | 10         | 2         |
| 2      | 2         | Mister Mental                     | 5.5        | 2         |
| 3      | 3         | Stumble and Fall                  | 8.5        | 2         |
| 4      | 4         | Last Train Home                   | 7          | 1         |
| 5      | 5         | C'mon C'mon                       | 6.5        | 2         |
| 6      | 6         | Sic Transit Gloria... Glory Fades | 5.5        | 2         |
| 7      | 7         | Megalomaniac                      | 4.5        | 2         |
| 8      | 8         | Escape Artists Never Die          | 3.5        | 2         |
| 9      | 9         | Paris & the New Math              | 2          | 1         |
| 10     | 10        | Kick It                           | 1          | 1         |
| 11     | 11        | What You Get                      | 8          | 1         |
| 12     | 12        | Maybe Someday                     | 3          | 1         |
| 13     | 13        | Followed the Waves                | 1          | 1         |
+--------+-----------+-----------------------------------+------------+-----------+
Using the SQL statement:

Code: Select all

SELECT * FROM `mdb_tracks_trc` INNER JOIN `mdb_artists_art` ON `mdb_tracks_trc`.`idart_trc` = `mdb_artists_art`.`id_art` SORT BY `rating_trc` DESC LIMIT 12
Gives me:

Code: Select all

+--------+-----------+-----------------------------------+------------+-----------+
| id_trc | idart_trc | name_trc                          | rating_trc | weeks_trc |
+--------+-----------+-----------------------------------+------------+-----------+
| 1      | 1         | Take Me Out                       | 10         | 2         |
| 3      | 3         | Stumble and Fall                  | 8.5        | 2         |
| 11     | 11        | What You Get                      | 8          | 1         |
| 4      | 4         | Last Train Home                   | 7          | 1         |
| 5      | 5         | C'mon C'mon                       | 6.5        | 2         |
| 2      | 2         | Mister Mental                     | 5.5        | 2         |
| 6      | 6         | Sic Transit Gloria... Glory Fades | 5.5        | 2         |
| 7      | 7         | Megalomaniac                      | 4.5        | 2         |
| 8      | 8         | Escape Artists Never Die          | 3.5        | 2         |
| 12     | 12        | Maybe Someday                     | 3          | 1         |
| 9      | 9         | Paris & the New Math              | 2          | 1         |
| 10     | 10        | Kick It                           | 1          | 1         |
+--------+-----------+-----------------------------------+------------+-----------+
I then want to re-order this final table by the `id_trc` field

Any ideas?

Thanks,
Rob
Last edited by purinkle on Fri Jul 13, 2007 12:49 pm, edited 1 time in total.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

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

Post by purinkle »

aceconcepts wrote:Take a look at: http://www.w3schools.com/sql/sql_orderby.asp
I understand where you are coming from... I think

You are suggesting something along the lines of:

Code: Select all

SELECT * FROM `mdb_tracks_trc` INNER JOIN `mdb_artists_art` ON `mdb_tracks_trc`.`idart_trc` = `mdb_artists_art`.`id_art` ORDER BY `rating_trc`, `id_trc` DESC LIMIT 12
But the problem lies in the fact that I want to do an ORDER BY, then a LIMIT, and then another ORDER BY

MySQL doesn't seem to want me to do this
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Your statement is a little confusing. Ordering is done, then the limit is done. How do you mean you want to order, limit, then order?
purinkle
Forum Newbie
Posts: 12
Joined: Fri Jul 13, 2007 6:54 am

Post by purinkle »

Everah wrote:Your statement is a little confusing. Ordering is done, then the limit is done. How do you mean you want to order, limit, then order?
Say I have 100 tracks in the database...

I want to order the tracks by their rating, then take the top twelve and reorder the top twelve by their id
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That's not going to happen. You need to take the top 12 of the data ordered by rating then by ID.
Post Reply