0

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;
3

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.

2
  • Pretty clever that.
    – OC2PS
    Dec 4 '14 at 10:33
  • 2
    Using language_id = 4 directly like that isn't standard SQL syntax (or at least I can name a few DBs that will reject it) so for compatibility in case you ever need to move to another DB use the more verbose (but functionally equivalent) CASE WHEN language_id = 4 THEN 1 ELSE 0 END. Dec 4 '14 at 10:40
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
1

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
2
  • I suppose UNION would combine the two queries. But wouldn't the performance still be kindof low i.e. equivalent of 2 queries? I suppose my objective is to access the table only once since both queries run on the same table.
    – OC2PS
    Dec 4 '14 at 10:34
  • 1
    That is not guaranteed to work: the row order resulting from any query (particularly when queries are combined with UNION rather than UNION ALL as without ALL it'll need to sort the rows to search for duplicates and may output the result in this new order it has picked) is not guaranteed unless you explicitly specify the desired order with an ORDER BY clause. Without ORDER BY it might work as you expect today, but at a later time (or in another DB entirely) the query planner may decide to take a different route and therefore emit the rows in a different order. Dec 4 '14 at 10:45
0

Another option is

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.