Single SQL query to retrieve data in a particular order (not ASC/DESC)

Posted on

Question :

I have a MySQL table, let’s say called reviews
review_id, product_id, language_id, rating, review, author
language_id ranges from 1 to 10 for 10 different languages.

Let’s say I determine that my visitor is browing the site using language whose id is 4

Is there a way to write a single query that:
1. First grabs the reviews for that product in current language
2. Next, grabs the reviews for that product in other languages sorted by language

So, basically does the job of the following 2 queries run one after the other:

SELECT * FROM reviews WHERE product_id = 723 AND language_id = 4;
SELECT * FROM reviews WHERE product_id = 723 AND language_id != 4 ORDER BY language_id ASC;

Answer :

You can simply do

SELECT * FROM reviews 
WHERE product_id = 723 
ORDER BY language_id = 4 DESC, language_id ASC;

language_id = 4 returns true or false, 1 or 0. Sort descending and you have language_id = 4 first, then all others.

I would do the following

SELECT *, CASE WHEN language_id = 4 THEN 0 ELSE 1 END Ord FROM reviews 
WHERE product_id = 723 
ORDER BY Ord, language_id

Use following query for example.

SELECT * FROM sakila.actor  a  where a.actor_id = 10
UNION
SELECT * FROM sakila.actor b where b.actor_id != 10

result of query

# actor_id, first_name, last_name, last_update
10, CHRISTIAN, GABLE, 2006-02-15 04:34:33
1, PENELOPE, GUINESS, 2006-02-15 04:34:33
2, NICK, WAHLBERG, 2006-02-15 04:34:33
3, ED, CHASE, 2006-02-15 04:34:33
4, JENNIFER, DAVIS, 2006-02-15 04:34:33
5, JOHNNY, LOLLOBRIGIDA, 2006-02-15 04:34:33
6, BETTE, NICHOLSON, 2006-02-15 04:34:33
7, GRACE, MOSTEL, 2006-02-15 04:34:33
8, MATTHEW, JOHANSSON, 2006-02-15 04:34:33
9, JOE, SWANK, 2006-02-15 04:34:33
11, ZERO, CAGE, 2006-02-15 04:34:33
12, KARL, BERRY, 2006-02-15 04:34:33

Another option is

SELECT * FROM reviews WHERE product_id = 723
order by case when language_id = 4 then 0 else 1 end, language_id;

Leave a Reply

Your email address will not be published. Required fields are marked *