how to get ordered results in mysql

Question

I have a db named library. There are authors, books, books_genres, genres, history

I have more 5 books in genre with name "Horror", 3 books in genre with name "comedy" and 2 books in genre with name "Romance"

I have a check boxes with all my genres and when i selected them i want the results to be grouped by genre with most records on in on TOP. Here is some code:

"SELECT title, price, genre_id, name
FROM books
INNER JOIN books_genres ON books.id = books_genres.book_id
INNER JOIN genres ON books_genres.genre_id = genres.id
WHERE genre_id IN ({$ids})";

this return to me all the books but without the order i want.

ids are string with genre ids like 1,2,3,4

see the answers here: http://stackoverflow.com/questions/27983/sql-group-by-with-an-order-by — elefont, Jun 26 '13 at 22:04

Yotam Omer · Accepted Answer · 2013-06-26T23:18:58.177

3

use MYSQL's ORDER BY as in:

SELECT ... FROM .... WHERE ... ORDER BY id DESC

DESC is for descending order / ASC is for ascending order

EDIT: try this:

"SELECT title, price, genre_id, name, (SELECT COUNT(*) FROM books_genres as b WHERE b.genre_id = genres.id) as genreCount
FROM books
INNER JOIN books_genres ON books.id = books_genres.book_id
INNER JOIN genres ON books_genres.genre_id = genres.id
WHERE genre_id IN ({$ids})
ORDER BY genreCount DESC, genres.id ASC;"

edited Jun 26 '13 at 23:18

answered Jun 26 '13 at 22:04

Yotam Omer

15,310
11
62
65

with this one i get results but not in wanted order – ValentinDamyanov Jun 26 '13 at 22:17
replace `id` with the name of the column you want to order by and `DESC` by `ASC` if you want the order to be ascending instead of descending. what is your wanted order? – Yotam Omer Jun 26 '13 at 22:20
i want the results to be grouped by genre with most records on in to be on TOP. So id or genre_id doesn't work well. – ValentinDamyanov Jun 26 '13 at 22:26
You'll have to change your query, one sec – Yotam Omer Jun 26 '13 at 22:28
Added query in answer. try it. – Yotam Omer Jun 26 '13 at 22:36
strange error this time: Unknown column 'genre_id' in 'where clause' – ValentinDamyanov Jun 26 '13 at 22:41
i don't have row named genre_id in books. genre_id is in books_genres (SELECT COUNT(*) FROM books as b WHERE b.genre_id = genres.id) – ValentinDamyanov Jun 26 '13 at 22:51
That explains alot.. edited. try again. next time try to be more clear. – Yotam Omer Jun 26 '13 at 23:07
I post all my tables above :) Everything works perfect now except "OREDER BY" :) Thanks ! – ValentinDamyanov Jun 26 '13 at 23:15

score 1 · Answer 2 · answered Jun 26 '13 at 22:08

1

You use a subquery and the ORDER BY clause.

For example:

"SELECT title, price, genre_id, name, (SELECT count(*) FROM books AS bks WHERE bks.genre_id = books.genre_id) AS genre_count
FROM books
INNER JOIN books_genres ON books.id = books_genres.book_id
INNER JOIN genres ON books_genres.genre_id = genres.id
WHERE genre_id IN ({$ids})
ORDER BY genre_count DESC"

answered Jun 26 '13 at 22:08

AlliterativeAlice

11,841
9
52
69

#1054 - Unknown column 'bks.genre_id' in 'where clause' – ValentinDamyanov Jun 26 '13 at 22:20

how to get ordered results in mysql

2 Answers2