sql-server - How to add sequence row numbers based on column values SQL Server

Question

I have the following table in SQL Server;

DesiredRowNo. Customer  Date        NID                 SID     Contacts  AG
 1               AAA    04/09/2018  15357989260016600   100743  1          1
 1               AAA    04/09/2018  15358110980018300   100743  1          1
 1               AAA    04/09/2018  15358197620012800   100743  1          1
 2               AAA    21/09/2018  15358902310016400   100743  1          1
 2               AAA    21/09/2018  15358999300015200   100743  1          1
 3               AAA    29/09/2018  15359624190012200   100743  1          1
 3               AAA    29/09/2018  15359896100010500   100702  1          1
 4               AAA    30/09/2018  15360551640011900   100743  1          1
 1               BBB    05/09/2018  15357989260016600   100743  1          1
 1               BBB    05/09/2018  15358110980018300   100743  1          1
 2               BBB    08/09/2018  15358197620012800   100743  1          1
 3               BBB    15/09/2018  15358902310016400   100743  1          1
 3               BBB    15/09/2018  15358999300015200   100743  1          1
 4               BBB    19/09/2018  15359624190012200   100743  1          1
 5               BBB    24/09/2018  15359896100010500   100702  1          1
 5               BBB    24/09/2018  15360551640011900   100743  1          1

I need the 1st column (DesiredRowNo.) to display a rank/row number based on the customer and the date. So for Customer AAA, all the rows with same date ranked as 1, then the next unique date ranked as 2, and so on. (has to be sequential based on date)

See the DesiredRowNo. values above for a better explanation as to what I'm trying to achieve.

Ive tried using;

ROW_NUMER() over (partition by Customer, date order by date) 

But this doesnt seem to give the correct sequencing as required.

Any ideas?

标签： sql-serverfunctionsequencepartitionrow-number

Answer 1

尝试这个 ：

 select  tab.DesiresRowNo.,final.Customer,
final.NID,final.SID,final.Contacts,final.AG
 from Table final
 left join 
 ( select
 ROW_NUMBER() OVER(ORDER BY Customer ASC) AS DesiresRowNo.,
 Customer,Date
 from Table
 group by Customer,Date )tab
 on final.Customer=tab.Customer
 and final.Date=tab.Date