MySQL, Selecting based on subquery using MIN() and GROUP BY

Question

First, to describe my data set. I am using SNOMED CT codes and trying to make a usable list out of them. The relevant columns are rowId, conceptID, and Description. rowId is unique, the other two are not. I want to select a very specific subset of those codes:

SELECT * 
  FROM SnomedCode 
  WHERE LENGTH(Description)=MIN(LENGTH(Description))
  GROUP BY conceptID

The result should be a list of 400,000 unique conceptIDs (out of 1.4 million) and the shortest applicable description for each code. The query above is obviously malformed (and would only return rows where LENGTH(description)=1 because the shortest description in the table is 1 character long.) What am I missing?

possible duplicate of [SQL Group By and min (MySQL)](http://stackoverflow.com/questions/11683712/sql-group-by-and-min-mysql) — Andriy M, Aug 23 '13 at 23:56
You need a correlated sub-query that will calculate your minimum for **each** `conceptID`. Another note: though mySQL allows to have in SELECT fields that are neither `grouped by` no `aggregated`, the result of such query in unpredictable as far as these fields are concerned. So **do not** use `SELECT *` with `GROUP BY`. — PM 77-1, Aug 24 '13 at 00:02

jaeheung · Answer 1 · 2013-08-24T00:46:43.927

SELECT conceptID, MAX(Description)
  FROM SnomedCode A
 WHERE LENGTH(Description)=(SELECT MIN(LENGTH(B.Description))
                              FROM SnomedCode B
                             WHERE B.conceptID = A.conceptID)
 GROUP BY conceptID

The "GROUP BY" and "MAX(Description)" are not really necessary, but were added as a tiebreaker for different descriptions with same length for a conceptID, as the requirements include unique conceptIDs.

MAX was chosen to penalize possible leading spaces. Otherwise MIN(Description) works as well.

BTW, this query takes quite some time if you have over million records. Test it with "AND conceptID in (list-of-conceptIDs-to-test)" added in the WHERE clause.

The table SnomedCode must have an index on conceptID. If not, the query will take forever.

MySQL, Selecting based on subquery using MIN() and GROUP BY

1 Answers1