Select by locale then language?

Question

Two tables:

a
  id
b
  id
  a_id
  url

The urls in table b have a pattern like:

.../en-us/...
.../en-gb/...
.../de-at/...
.../de-de/...
.../ja-jp/...

Query:

SELECT b.url
FROM a
JOIN b ON b.a_id = a.id

How can I further narrow these results to select the url like %/en-us/% first, then like %/en-%/% if en-us does not exist? In other words, if I have two urls, one for both en-us and en-gb, how can I "prioritize" en-us over en-gb and only show one or the other?

Use an `IF` statment in the `WHERE` clause --> https://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause — Zak, Jul 31 '18 at 16:24
I think you would need a rank table to assign ranks to ur URLs based on your custom business logic. Get ranks on ur URLs post joining the and then try to sort in the order of RANK — Rhythem Aggarwal, Jul 31 '18 at 16:26

score 2 · Accepted Answer · answered Jul 31 '18 at 16:26

2

You can create a custom sorting order with a case expression and a bunch of conditions:

SELECT   b.url
FROM     a
JOIN     b ON b.a_id = a.id
ORDER BY CASE WHEN b.url LIKE '%/en-us/%' THEN 0
              WHEN b.url LIKE '%/en-gb/%' THEN 1
              WHEN b.url LIKE '%/en-%/%'  THEN 2
              ELSE                             3
         END

answered Jul 31 '18 at 16:26

Mureinik

297,002
52
306
350

How does this work? I have seen this for the first time and looks solution to a lot of my issues. – Rhythem Aggarwal Jul 31 '18 at 16:28
@RhythemAggarwal what exactly are you asking about? The ordering? the `case` expression? – Mureinik Jul 31 '18 at 16:29
Yes exactly. I have always used order by clause on absolute columns. or, if i were to do this, i would write the case statement inside select statement. and then order by on it. – Rhythem Aggarwal Jul 31 '18 at 16:31

score 1 · Answer 2 · answered Jul 31 '18 at 16:26

Use a CASE expression with ORDER BY:

SELECT b.url
FROM a
INNER JOIN b
    ON a.id = b.a_id
ORDER BY
    CASE WHEN b.url LIKE '%/en-us/%' THEN 0 ELSE 1 END;

If you really only want to show one URL type, then you probably want a WHERE clause, something like:

WHERE b.url LIKE '%/en-us/%`

Select by locale then language?

2 Answers2