1
\$\begingroup\$

I've a table with plant names where I need to select one English synonym for each code:

| identifier  | code  | lang | langno | status |
|-------------|-------|------|--------|--------|
| Nr80127     | XYOSC | la   | 1      | A      |
| Nr80128     | XYOSC | la   | 2      | A      |
| Nr424832    | XYOSC | la   |        | A      |
| Nr424833    | XYOSC | la   |        | A      |
| Nr80130     | XYOSE | en   | 1      | A      |
| Nr80131     | XYOSE | en   | 2      | A      |
| Nr80132     | XYOSE | en   | 3      | A      |
| Nr80133     | XYOSE | en   | 4      | A      |
| Nr80134     | XYOSE | en   | 5      | A      |
| Nr80145     | XYOSE | la   | 1      | A      |
| Nr80146     | XYOSE | la   | 2      | A      |
| Nr187899    | XYOSE | en   | 6      | A      |
| Nr374061    | XYOSE | la   |        | A      |
| Nr80154     | XYOSI | la   | 1      | A      |
| Nr282209    | XYOSI | la   | 2      | A      |
| Nr316394    | XYOSI | en   |        | A      |
| Nr316395    | XYOSI | en   |        | A      |
| Nr282196    | XYOSM | la   | 1      | A      |
| Nr424840    | XYOSM | en   |        | A      |
| Nr424841    | XYOSM | la   |        | A      |
| Nr424842    | XYOSM | la   |        | A      |
| Nr424843    | XYOSM | la   |        | A      |
| Nr80157     | XYOSQ | la   | 1      | A      |
| Nr80158     | XYOSQ | la   | 2      | A      |
| Nr282201    | XYOSQ | la   | 3      | A      |
| Nr374060    | XYOSQ | en   |        | A      |
| Nr80161     | XYOSS | en   | 1      | N      |
| Nr80163     | XYOSS | la   | 1      | A      |
| Nr80165     | XYOST | en   | 1      | N      |
| Nr80166     | XYOST | en   | 2      | N      |
| Nr80167     | XYOST | en   | 3      | N      |
| Nr80172     | XYOST | la   | 1      | A      |
| Nr80173     | XYOST | la   | 2      | A      |
| Nr244045    | XYOST | en   | 4      | N      |
| Nr244046    | XYOST | en   | 5      | A      |
| Nr244047    | XYOST | en   | 6      | A      |
| Nr281544    | XYOST | en   | 7      | A      |
| Nr374023    | XYOST | en   |        | A      |
| Nr282200    | XYOSV | la   | 1      | A      |

What I need to do:

  1. Use only rows where lang = "en" AND status = "A".
  2. For each code select the row with lowest langno (consider empty ("") as 0) e.g. "Nr80130" for "XYOSE" (langno = 1).

    So far I can use this query:

    SELECT table_name.identifier,
                      table_name.code
                 FROM table_name
                      JOIN
                      (
                          SELECT code,
                                 min(CASE langno WHEN "" THEN 0 ELSE langno END) AS newLangno
                            FROM table_name
                           WHERE lang = "en" AND 
                                 status = "A"
                           GROUP BY code
                      )
                      AS FirstLangno ON (table_name.code = FirstLangno.code AND 
                                         table_name.langno = (CASE FirstLangno.newLangno WHEN 0 THEN "" ELSE FirstLangno.newLangno END) ) 
                WHERE table_name.lang = "en";
    
  3. However, there are also cases when there are multiple rows with the same langno (e.g. "XYOSI", where "Nr316394" and "Nr316395" both have empty langno), in this case I need to select the one with lowest identifier.

I've managed to solve it using this query, however, I'm wondering whether there is a more efficient / elegant way for that?

SELECT min(identifier) AS identifier,
       code
  FROM (
           SELECT table_name.identifier,
                  table_name.code
             FROM table_name
                  JOIN
                  (
                      SELECT code,
                             min(CASE langno WHEN "" THEN 0 ELSE langno END) AS newLangno
                        FROM table_name
                       WHERE lang = "en" AND 
                             status = "A"
                       GROUP BY code
                  )
                  AS FirstLangno ON (table_name.code = FirstLangno.code AND 
                                     table_name.langno = (CASE FirstLangno.newLangno WHEN 0 THEN "" ELSE FirstLangno.newLangno END) ) 
            WHERE table_name.lang = "en" 
       )
 GROUP BY code;

My data has around 400 k rows, and of course real data contains more columns.

\$\endgroup\$
  • \$\begingroup\$ "and of course real data contains more columns." So why didn't you include those for a fuller picture? \$\endgroup\$ – Mast Nov 29 '17 at 9:03
  • \$\begingroup\$ @mast because those are not relevant to this question. \$\endgroup\$ – Máté Juhász Nov 29 '17 at 9:39
  • \$\begingroup\$ Your call, but now it's possible you'll get answers that won't help in your situation because the presented situation is different from yours. \$\endgroup\$ – Mast Nov 29 '17 at 9:55
  • \$\begingroup\$ I need (maximum ) one identifier for each code. Other columns doesn't matter. \$\endgroup\$ – Máté Juhász Nov 29 '17 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.