CODEWITHSUNDEEP
sqlsql-servert-sqlselectdistinct
ahmd0
ahmd0

Reputation: 17293

Select DISTINCT from two columns in t-SQL

Say, if I have the following table:

CREATE TABLE tbl (ID INT, Type UNIQUEIDENTIFIER)
INSERT tbl VALUES
(1, N'D9D09D5B-AF63-484C-8229-9762B52972D0'),
(2, N'D9D09D5B-AF63-484C-8229-9762B52972D6'),
(3, N'D9D09D5B-AF63-484C-8229-9762B52972D9'),
(3, N'D9D09D5B-AF63-484C-8229-9762B52972D2'),
(4, N'D9D09D5B-AF63-484C-8229-9762B52972D0')

and I need to select distinct ID columns but also whatever the Type column value that is associated with it. If I do the following:

select distinct id, type from tbl

It returns the whole table when I need only this:

1, N'D9D09D5B-AF63-484C-8229-9762B52972D0'
2, N'D9D09D5B-AF63-484C-8229-9762B52972D6'
3, N'D9D09D5B-AF63-484C-8229-9762B52972D9'
4, N'D9D09D5B-AF63-484C-8229-9762B52972D0'

I know it must be something simple, but what am I missing here?

Upvotes: 1

Views: 5500

Answers (2)

Himanshu
Himanshu

Reputation: 32602

As per you comment you need to want to select first type in the list. So you can achieve this by using subquery like this:

SELECT id, (SELECT TOP 1 type FROM tbl a WHERE id = b.id)
FROM tbl b GROUP BY id

See this SQLFiddle

Upvotes: 3

Conrad Lotz
Conrad Lotz

Reputation: 8818

select id, min(type) from tbl group by id

Upvotes: 2

Related Questions