SQL: remove duplicate values from a select based on 2 fields
I have this simple table (omitti开发者_C百科ng primary id field):
value - language - externID
1 default 10
1 english 10
2 english 10
3 default 10
I need a SQL that select every values that got language IN ('default','english') But in the case the same value got both default and english the sql should discard the "defaul" record and return only the english one.
example: SELECT * FROM tbl WHERE externID = 10 AND language IN ('default','english') AND "prefer english if there is a record with same value with default"
Result of the SELECT:
1 - english - 10
2 - english - 10
3 - default - 10
Is there any way to do it? Thanks
It's like doing a GROUP BY value But with the preference of the english language.
ps. "english" is an example I need I can choose the specific language between vary
For this particular question max(language)
should work because d is less than e.
EDIT (Sollution that won't break):
Add max(case language when 'default' then 0 else 1 end)
Edit2 (Even better):
Add max(case language when 'english' then 1 else 0 end)
so you won't even get diplicate values if you add more languages to your in()
section
EDIT Nr. 2:
As i promised, here i am with a fresh new mondaymorning mind and got the right query for your needs :-)
Try
SELECT value,
CASE group_concat(language)
WHEN 'default' then 'default'
ELSE LTRIM(REPLACE(group_concat(language SEPARATOR ' '),'default',''))
END as language,
externID
FROM demo
WHERE externID = 10
AND language in ('default','english')
GROUP BY value;
Explanation: GROUP_CONCAT and LTRIM
精彩评论