开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜