SQLTeam.com | Weblogs | Forums

DISTINCT Row


#1

Dear Friends;
I have table contain
ID , Date , DataChange
1 2018-07-01 a
2 2018-07-02 a
2 2018-07-02 a
3 2018-07-04 b
4 2018-07-06 a
5 2018-07-09 c
5 2018-07-10 c
5 2018-07-11 d
i need query get DISTINCT date (DataChange only)
ID , Date , DataChange
1 2018-07-01 a
3 2018-07-04 b
4 2018-07-06 a
5 2018-07-09 c
5 2018-07-11 d


#2
WITH cte
AS (SELECT *,
           ROW_NUMBER() OVER (PARTITION BY d.id, d.datachange ORDER BY date) AS rn,
           COUNT(*) OVER (PARTITION BY d.id, d.date, d.datachange) AS ct
    FROM mytable)
SELECT *
FROM cte
WHERE rn = 1
      AND ct = 1;

#3
SELECT ID, Date, DataChange
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY DataChange ORDER BY Date) AS row_num
    FROM dbo.TableName
) AS derived
WHERE row_num = 1