mySQL SELECT WHERE CASE

Asked by At

I want to make a simple select from a table based on a conditional where clause.

I do like this:

CREATE TABLE TEST (SOMETHING TEXT, NAME TEXT, LANG INT(2));

INSERT INTO TEST (SOMETHING, NAME, LANG) VALUES ('123','US',1);
INSERT INTO TEST (SOMETHING, NAME, LANG) VALUES ('234','US',2);
INSERT INTO TEST (SOMETHING, NAME, LANG) VALUES ('345','EU',2);

I want to select the NAME in LANG=1 if available otherwise in LANG=2.

I am doing this:

SELECT * FROM TEST WHERE CASE
WHEN LANG=1 THEN LANG=1
ELSE LANG=2
END

I get this:

123 US 1
234 US 2
345 EU 2

But want this:

123 US 1
345 EU 2

Should I use a GROUP HAVING or how?

Most appreciate your help.

3 Answers

0
forpas On Best Solutions

You can do it with EXISTS:

select t.*
from test t
where not exists (
  select 1 from test 
  where something <> t.something and name = t.name and lang = 1 
)  

See the demo.
Results:

| SOMETHING | NAME | LANG |
| --------- | ---- | ---- |
| 123       | US   | 1    |
| 345       | EU   | 2    |
0
Nick On

You need to self-join your table to a list of the minimum LANG values by NAME, then you can select the SOMETHING value associated with that LANG value:

SELECT T1.*
FROM TEST T1
JOIN (SELECT NAME, MIN(LANG) AS LANG
      FROM TEST
      GROUP BY NAME) T2 ON T2.NAME = T1.NAME AND T2.LANG = T1.LANG

This will return the minimum LANG value for each NAME, so if a value of 1 exists it will return that, otherwise it will return 2.

Output:

SOMETHING   NAME    LANG
345         EU      2
123         US      1

Demo on dbfiddle

0
Tim Biegeleisen On

We can handle this using ROW_NUMBER:

SELECT SOMETHING, NAME, LANG
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY LANG) rn
    FROM yourTable
) t
WHERE rn = 1;

This is probably the most performant way to handle your problem in MySQL, assuming you are using MySQL8+.