How to select * but distinct pair of two columns in sql?

Asked Apr 13 '21 at 19:37

Active Apr 13 '21 at 19:46

Viewed 10 times

I want to see unique descritions for each collection_id. It'd be okay if names are duplicate but descriptions can not be allowed. I tried disctinct but it doesn't allow me to select all the columns.

id	collection_id	description	name
1	12	ABC	X
2	13	ABD	Y
3	12	ABC	X
4	13	LMN	Y
5	14	OPQ	X
6	14	OPQ	Y

To this

id	collection_id	description	name
1	12	ABC	X
2	13	ABD	Y
4	13	LMN	Y
5	14	OPQ	X

edited Apr 13 '21 at 19:46

asked Apr 13 '21 at 19:37

Scapedee

What do you mean by "for each collection_id"? You don't have `15` in the desired result. – Barmar Apr 13 '21 at 19:40
It looks like you just want `GROUP BY description` – Barmar Apr 13 '21 at 19:41
@Barmar Sorry just updated it – Scapedee Apr 13 '21 at 19:44
So you want `GROUP BY collection_id, description`. – Barmar Apr 13 '21 at 19:45
@barmar, but can I select * in that case? – Scapedee Apr 13 '21 at 19:46
Yes, see the linked question for how to get all the columns of the row. – Barmar Apr 13 '21 at 19:47
that question ignores the different values in other_columns – Scapedee Apr 13 '21 at 20:12
No it doesn't. It returns an entire row in each group. – Barmar Apr 13 '21 at 20:14
I meant I want unique 'description' values for each group. No matter how many they are. – Scapedee Apr 13 '21 at 20:17
That's what it does. It prints one row for each group. You need to specify some criteria for which row in the group, like highest or lowest `id`. – Barmar Apr 13 '21 at 20:18

How to select * but distinct pair of two columns in sql?

0 Answers0