Select distinct records based on several columns

Transact-SQL
,
1

I need to select unique records from below table

CREATE TABLE #tb1 (date DATETIME , ref1 nvarchar(50),ref2 nvarchar(50), Name nvarchar(50), amount INT )

INSERT INTO #tb1 VALUES
('2017-01-01 08:00','My Ref1','My Second Ref1', 'Mr. A',500),
('2017-01-01 08:01','My Ref1','My Second Ref1', 'Mr. B',500),
('2017-02-01 08:00','My Ref2','My Second Ref2', 'Mr. A',500),
('2017-02-01 08:02','My Ref2','My Second Ref2', 'Mr. B',500),
('2017-02-01 08:01','My Ref2','My Second Ref3', 'Mr. A',500),
('2017-02-01 08:01','My Ref2','My Second Ref3', 'Mr. B',500),
('2017-03-01 08:01','My Ref5','My Second Ref5', 'Mr. C',500),
('2017-03-01 08:02','My Ref5','My Second Ref5', 'Mr. C',500)

Result i,m expecting is as follows
('2017-01-01 08:00','My Ref1','My Second Ref1', 'Mr. A',500)
('2017-02-01 08:00','My Ref2','My Second Ref2', 'Mr. A',500)
('2017-02-01 08:01','My Ref2','My Second Ref3', 'Mr. A',500)
('2017-03-01 08:01','My Ref5','My Second Ref5', 'Mr. C',500)
('2017-03-01 08:02','My Ref5','My Second Ref5', 'Mr. C',500)

  1. Each record should have unique ref1 & ref2 ( so i exclude 2, 4 & 6 rows)
  2. And if same name has multiple records with unique ref1 & ref2, but with different datetime then all such records has to be selected. ( So i select both 7th & 8th Records)

How can i write a select statement for this.

Highly appreciate your help for this..
Thanks
klllmmm

2

Try this:

select [date]
      ,ref1
      ,ref2
      ,[name]
      ,amount
  from (select [date]
              ,ref1
              ,ref2
              ,[name]
              ,amount
              ,rank() over(partition by ref1
                                       ,ref2
                               order by [name]
                          ) as r
          from #tb1
       ) as a
 where r=1
;
3

Thank you very much..!