Justin Justin - 5 months ago 8
SQL Question

T-SQL SELECT TOP 1 DISTINCT for repeating

I need to return only the first row of each repeating number apart from zeros in the query :

DECLARE @Test TABLE

(
ID NVARCHAR(10)
,Number INT

)
INSERT INTO @Test VALUES ('TG32',6)
INSERT INTO @Test VALUES ('TG47',6)
INSERT INTO @Test VALUES ('AG9',6)
INSERT INTO @Test VALUES ('BF27',2)
INSERT INTO @Test VALUES ('QD65',2)
INSERT INTO @Test VALUES ('F98',0)
INSERT INTO @Test VALUES ('GC5',0)
INSERT INTO @Test VALUES ('HT76',0)
INSERT INTO @Test VALUES ('KL81',0)
INSERT INTO @Test VALUES ('WR52',0)

SELECT * FROM @Test


I'm trying to get :

ID Number

TG32 6
BF27 2
F98 0
GC5 0
HT76 0
KL81 0
WR52 0

Answer

You can make this using ROW_NUMBER and sub query.

SELECT ID, Number
FROM (
    SELECT  *, 
            ROW_NUMBER() OVER (PARTITION BY Number ORDER BY (SELECT 1))as RN
    FROM @test) as d
WHERE (CASE WHEN d.Number = 0 THEN 1 ELSE d.RN END) = 1