CODEWITHSUNDEEP
mysqlsql
RD Ward
RD Ward

Reputation: 6747

SELECT DISTINCT combination of distinct values, plus non-distinct values?

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.

Upvotes: 1

Views: 3837

Answers (5)

Matthew
Matthew

Reputation: 26

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

Upvotes: 0

Skrol29
Skrol29

Reputation: 5597

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

Upvotes: 1

Jeff
Jeff

Reputation: 5993

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

Upvotes: 0

Nicola Cossu
Nicola Cossu

Reputation: 56397

SELECT report, topic, lat, lng, event, 
group_concat(docID) as ids 
FROM reports
group by report, topic, lat, lng, event

Upvotes: 4

Thomas Kelley
Thomas Kelley

Reputation: 10302

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.

Upvotes: 1

Related Questions