[SOLVED] Retrieving and sorting data from MySQL
Posted: Fri Jul 13, 2007 9:14 am
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:
Using the SQL statement:
Gives me:
I then want to re-order this final table by the `id_trc` field
Any ideas?
Thanks,
Rob
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 |
+--------+-----------+-----------------------------------+------------+-----------+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 12Code: 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 |
+--------+-----------+-----------------------------------+------------+-----------+Any ideas?
Thanks,
Rob