Raheem Sarwar Raheem Sarwar - 4 years ago 69
MySQL Question

How to select distinct rows per group

My table looks as follows:

author_id | doc_id | lang
-----------+--------+----------------

53 | 12642 | English
53 | 12643 | English
53 | 75 | French

55 | 12605 | German
55 | 12606 | German
55 | 12596 | English


and I need to write a query make a table as follows:

author_id | doc_id | lang
-----------+--------+----------------

53 | 12642 | English
53 | 75 | French

55 | 12605 | German
55 | 12596 | English

Answer Source

If I understand correctly, for each combination of author and language, you want the minimal document id. You could group by the columns you want the distinct values of, and apply an aggregate min to the other:

SELECT   author_id, MIN(doc_id), lang
FROM     docs
GROUP BY author_id, lang
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download