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

All we need is an easy explanation of the problem, so here it is.

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;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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

Method 3

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

Method 4

Another option is

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply