1

I am having a lot of trouble finding any documentation on executing a simple MySQL query that will return all distinct values from a combination, but also return another column that is not distinct.

In my case report, topic, lat, lng, event must be the distinct combination of values, but I also want to return the docID column.

If I include docID as a distinct value, it returns every document, because each docID is unique.

SELECT distinct report, topic, lat, lng, event, /*and each of their*/ docID FROM reports

I know this question is really simple, but I just can't find any documentation about it.

Ryan Ward Valverde
  • 6,458
  • 6
  • 37
  • 48
  • Seems possible I suppose, but it strikes me odd that there is no built in mechanism for dealing with this. – Ryan Ward Valverde Jun 09 '11 at 23:16
  • Then you should decide if you really need to use DISTINCT, if you want the `docID` column, then it will need to get all values in that column, unless you want the MINinum or MAXimum, or some other aggregate on the `docID` column. – Xint0 Jun 09 '11 at 23:18
  • Thanks everybody, I guess I just couldn't get out of the wrong mindset. – Ryan Ward Valverde Jun 09 '11 at 23:24

5 Answers5

4
SELECT report, topic, lat, lng, event, 
group_concat(docID) as ids 
FROM reports
group by report, topic, lat, lng, event
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
1

Try using GROUP BY:

SELECT report, topic, lat, lng, event, docID
FROM reports
GROUP BY report, topic, lat, lng, event

But be aware that if a docID is duplicated among a particular grouping, only one value will show up.

Thomas Kelley
  • 10,187
  • 1
  • 36
  • 43
1

The logic can give you your answer.

If several docs has the same combination of (report, topic, lat, lng, event), then which docId would you want to see of them ?

If you want all of them then you don't want a real distinct.

If you want only one (or a concat, or any operation), then MySQL cannot choose it arbitrarily for you, you have to query it (MIN, MAX, FIRST, GROUP_CONCAT, ...).

Then the reply is : you must use a GROUP clause. Like :

SELECT report, topic, lat, lng, event, MIN(docId) AS docIdMin 
FROM my_table
GROUP BY report, topic, lat, lng, event
Skrol29
  • 5,402
  • 1
  • 20
  • 25
0
select report, topic, lat, lng, event, max(docID)
From reports
group by report, topic, lat, lng, event

Checkout the Group By http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max

Jeff
  • 5,913
  • 2
  • 28
  • 30
0

I find I solve this with a group by and a Max or Min because for each distinct line there may be several DocId values.

SELECT report, topic, lat, lng, event, Max(docID) FROM reports
group by report, topic, lat, lng, event
ib.
  • 27,830
  • 11
  • 80
  • 100
Matthew
  • 26
  • 1