CODEWITHSUNDEEP
mysqlgroup-by
Yannis P.
Yannis P.

Reputation: 2765

MySQL: Select on GROUP BY only one row with certain criteria

I am having a table with documents where each document has a doc_id but on the same date for the same case_id I might be having two different language versions

doc_id      case_id     date        lang
001-89259   1012/02     2008-11-04  FRA
001-144945  10122/04    2014-06-19  ENG
001-57558   10126/82    1988-06-21  ENG
001-62116   10126/82    1988-06-21  FRA
001-91708   10129/04    2009-03-10  FRA
001-116955  10131/11    2013-03-07  FRA
001-102676  10143/07    2011-01-11  FRA
001-104520  10145/07    2011-04-12  FRA
001-72756   10162/02    2006-03-09  FRA
001-72757   10162/02    2006-03-09  ENG
001-82198   10163/02    2007-09-06  ENG
001-57555   10208/82    1988-05-26  ENG
001-62113   10208/82    1988-05-26  FRA

What I want to do is to select the english version, if available, per case_id, date, otherwise keep the french. My output would then look like:

doc_id      case_id     date        lang
001-89259   1012/02     2008-11-04  FRA
001-144945  10122/04    2014-06-19  ENG
001-57558   10126/82    1988-06-21  ENG -- keep only the english version
001-91708   10129/04    2009-03-10  FRA
001-116955  10131/11    2013-03-07  FRA
001-102676  10143/07    2011-01-11  FRA
001-104520  10145/07    2011-04-12  FRA
001-72757   10162/02    2006-03-09  ENG -- keep only the english version
001-82198   10163/02    2007-09-06  ENG
001-57555   10208/82    1988-05-26  ENG -- keep only the english version

How can I do it with MySQL?

UPDATE:

All answers give the correct result but I marked Görkem's as correct as IMO is the most elegant and straight-forward as of why it works.

I initially accepted Görkem's answer but for some reason it returned one wrong result that Strawberry pointed out. That leaves Strawberry's answer as the most elegant and correct

Upvotes: 0

Views: 65

Answers (4)

Strawberry
Strawberry

Reputation: 33945

SELECT DISTINCT COALESCE(e.doc_id,f.doc_id) doc_id
              , f.case_id
              , f.date
              , COALESCE(e.lang,f.lang) lang 
           FROM my_table f 
           LEFT 
           JOIN my_table e 
             ON e.case_id = f.case_id 
            AND e.date = f.date 
            AND e.lang = 'ENG';

Upvotes: 2

Görkem D.
Görkem D.

Reputation: 551

SELECT 
  doc_id,
  case_id,
  date,
  lang,
  max(case lang when 'ENG' then 1 else 0 end)
FROM tbl
GROUP BY case_id

Upvotes: 1

Ashutosh Singh
Ashutosh Singh

Reputation: 21

If this SQL is required for some research, there is a way to get the expected result set:

Select SUBSTRING_INDEX(GROUP_CONCAT(doc_id ORDER BY lang ), ',', 1) doc_id, case_id, date, SUBSTRING_INDEX(GROUP_CONCAT(lang ORDER BY lang), ',', 1) lang from table group by case_id,date

Upvotes: 1

Deep
Deep

Reputation: 2512

SELECT
        sorted.doc_id,
        sorted.case_id,
        sorted.date,
        sorted.lang
    FROM (
        SELECT
                doc_id,
                case_id,
                date,
                lang
            FROM tbl
            ORDER BY FIELD(lang, 'ENG', 'FRA')
    ) sorted
    GROUP BY sorted.case_id

Upvotes: 1

Related Questions