# Find record with minimum value in column(s)

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.

• "and of course real data contains more columns." So why didn't you include those for a fuller picture? – Mast Nov 29 '17 at 9:03
• @mast because those are not relevant to this question. – Máté Juhász Nov 29 '17 at 9:39
• 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. – Mast Nov 29 '17 at 9:55
• I need (maximum ) one identifier for each code. Other columns doesn't matter. – Máté Juhász Nov 29 '17 at 10:59