Benjamin Sowden Benjamin Sowden - 1 month ago 6
SQL Question

SQL Counting Distinct Rows and outputting answers

I am trying to count distinct rows in table in sql that will output a value that corresponds to the orders..

I have CustomerID and OrderNumber and if a customer places more than 1 order in the system on a particular day I want to be able to label each distinct order with a number like below (The number will need to increase with every change or ordernumber from multiple rows (As the rows also contain each item purchased)

OrderNumber CustomerID Seq Number
BINV0024536 FOBJAMBLU 1
BINV0024536 FOBJAMBLU 1
BINV0024789 FOBJAMBLU 2
BINV0024789 FOBJAMBLU 2

Answer

For SQL SERVER you may use DENSE_RANK().

SELECT OrderNumber, CustomerID, 
DENSE_RANK() OVER(ORDER BY CustomerID, OrderNumber) AS  Seq 
FROM [YOURTABLE]

If you want to reset you seq with different customerID then you also have to use PARTITION

SELECT OrderNumber, CustomerID, 
DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY CustomerID, OrderNumber) AS  Seq 
FROM [YOURTABLE]
Comments