CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
user1327064
user1327064

Reputation: 4337

Get list of unique records using TSQL?

I have 1000s of record in a table. Here is the table structure and records:-

Table_01

CREATE TABLE Table_Temp
(
    ID int,
    Name varchar(50),
    Tech varchar(50)
    CONSTRAINT pk_table_01_ID PRIMARY KEY (ID)
)
GO

Insert records:-

INSERT [dbo].[Table_Temp] ([ID], [Name], [Tech]) VALUES (1, N'Apple', N'Kim')
GO
INSERT [dbo].[Table_Temp] ([ID], [Name], [Tech]) VALUES (2, N'Orange', N'Kim')
GO
INSERT [dbo].[Table_Temp] ([ID], [Name], [Tech]) VALUES (3, N'Pear', N'Paul')
GO
INSERT [dbo].[Table_Temp] ([ID], [Name], [Tech]) VALUES (4, N'Blueberry', N'Paul')
GO
INSERT [dbo].[Table_Temp] ([ID], [Name], [Tech]) VALUES (5, N'Blackberry', N'Paul')
GO
INSERT [dbo].[Table_Temp] ([ID], [Name], [Tech]) VALUES (6, N'Grapes', N'Sam')


ID  Name           Tech
1   Apple          Kim
2   Orange         Kim
3   Pear           Paul
4   Blueberry      Paul
5   Blackberry     Paul
6   Grapes         Sam

I need unique entries by Tech column and I need ID and Tech as my return values. Here is query I am using:-

SELECT distinct Tech, ID FROM Table_Temp

but result I am getting is not expected. Here is what I am expecting and unique records by Tech also returns ID,Tech together.

Result Expected:-

Kim, 1
Paul, 3
Sam, 6

Upvotes: 0

Views: 150

Answers (2)

László Koller
László Koller

Reputation: 1159

It's not 100% clear from your original posting what you are looking for, but based on the result set that you are expecting I believe that you need to modify your query as follows:

SELECT Tech, Min(ID)
FROM Table_Temp
GROUP BY Tech

(NOTE: I went with the assumption that "Grapes" returned for Sam was a typo, and that you actually intended to expect "6" as the value associated with Sam's entry.)

Upvotes: 1

marc_s
marc_s

Reputation: 754468

If you're using SQL Server 2005 or newer and you want to have uniqueness by Tech - then use something like this:

;WITH UniqueByTech AS
(
    SELECT
        Tech, Name, ID,
        RN = ROW_NUMBER() OVER (PARTITION BY Tech ORDER BY ID)
    FROM dbo.Table_Temp
)
SELECT Tech, ID
FROM UniqueByTech
WHERE RN = 1

But I'm still not understanding why you seem to be returning the ID for Kim and Paul but not for Sam - what's the logic ??

OK, with the update, now my CTE (Common Table Expression) shown here should give you what you're looking for.

The CTE basically "partitions" the data by whatever you want to have unique - here the Tech column - and then it numbers all rows in each partition starting from 1. If you want only 1 row per "partition", then just select the row with the ROW_NUMBER() equal to 1

Upvotes: 1

Related Questions