SQL Server: how to create sequence number column

Question

I have a Sales table with the following data:

| SalesId | CustomerId | Amount |  
|---------|------------|--------|  
| 1       | 1          | 100    |  
| 2       | 2          | 75     |  
| 3       | 1          | 30     |  
| 4       | 3          | 49     |  
| 5       | 1          | 93     |

I would like to insert a column into this table that tells us the number of times the customer has made a purchase. So it'll be like:

| SalesId | CustomerId | Amount | SalesNum |  
|---------|------------|--------|----------|  
| 1       | 1          | 100    | 1        |  
| 2       | 2          | 75     | 1        |  
| 3       | 1          | 30     | 2        |  
| 4       | 3          | 49     | 1        |  
| 5       | 1          | 93     | 3        |

So I can see that in salesId = 5, that is the 3rd transaction for customerId = 1. How can I write such a query to insert / update such column? I am on MS SQL but I am also interested in the MYSQL solution should I need to do this there in the future.

Thank you.

ps. Apology for the table formatting. Couldn't figure out how to format it nicely.

GarethD · Accepted Answer · 2014-10-09 10:48:53Z

You need ROW_NUMBER() to assign a sequence number. I'd strongly advise against storing this value though, since you will need to recalculate it with every update, instead, you may be best off creating a view if you need it regularly:

CREATE VIEW dbo.SalesWithRank
AS
    SELECT  SalesID,
            CustomerID,
            Amount,
            SalesNum = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesID)
    FROM    Sales;
GO

SQL Server Example on SQL Fiddle

ROW_NUMBER() will not assign duplicates in the same group, e.g. if you were assigning the rows based on Amount and you have two sales for the same customer that are both 100, they will not have the same SalesNum, in the absence of any other ordering criteria in your ROW_NUMBER() function they will be randomly sorted. If you want Sales with the same amount to have the same SalesNum, then you need to use either RANK or DENSE_RANK. DENSE_RANK will have no gaps in the sequence, e.g 1, 1, 2, 2, 3, whereas RANK will start at the corresponding position, e.g. 1, 1, 3, 3, 5.

If you must do this as an update then you can use:

WITH CTE AS
(   SELECT  SalesID,
            CustomerID,
            Amount,
            SalesNum,
            NewSalesNum = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesID)
    FROM    Sales
)
UPDATE  CTE
SET     SalesNum = NewSalesNum;

SQL Server Update Example on SQL Fiddle

MySQL Does not have ranking functions, so you need to use local variables to achieve a rank by keeping track of the value from the previous row. This is not allowed in views so you would just need to repeat this logic wherever you needed the row number:

SELECT  s.SalesID,
        s.Amount,
        @r:= CASE WHEN @c = s.CustomerID THEN @r + 1 ELSE 1 END AS SalesNum,
        @c:= CustomerID AS CustomerID
FROM    Sales AS s
        CROSS JOIN (SELECT @c:= 0, @r:= 0) AS var
ORDER BY s.CustomerID, s.SalesID;

The order by is critical here, which means in order to order the results without affecting the ranking you need to use a subquery:

SELECT  SalesID,
        Amount,
        CustomerID,
        SalesNum
FROM    (   SELECT  s.SalesID,
                    s.Amount,
                    @r:= CASE WHEN @c = s.CustomerID THEN @r + 1 ELSE 1 END AS SalesNum,
                    @c:= CustomerID AS CustomerID
            FROM    Sales AS s
                    CROSS JOIN (SELECT @c:= 0, @r:= 0) AS var
            ORDER BY s.CustomerID, s.SalesID
        ) AS s
ORDER BY s.SalesID;

MySQL Example on SQL Fiddle

Again, I would recommend against storing the value, but if you must in MySQL you would use:

UPDATE  Sales
        INNER JOIN
        (   SELECT  s.SalesID,
                    @r:= CASE WHEN @c = s.CustomerID THEN @r + 1 ELSE 1 END AS NewSalesNum,
                    @c:= CustomerID AS CustomerID
            FROM    Sales AS s
                    CROSS JOIN (SELECT @c:= 0, @r:= 0) AS var
            ORDER BY s.CustomerID, s.SalesID
        ) AS s2
            ON Sales.SalesID = s2.SalesID
SET     SalesNum = s2.NewSalesNum;

MySQL Update Example on SQL Fiddle

arunbabu · Accepted Answer · 2014-10-09 10:25:46Z

2

Using Subquery,

   Select *, (Select count(customerid) 
               from ##tmp t
               where t.salesid <= s.salesid
               and      t.customerid = s.customerid)
from ##tmp s

answered Oct 9, 2014 at 10:25

arunbabu

2893 silver badges15 bronze badges

1 Comment

Joachim Isaksson Over a year ago

+1 Slower, but should run on both SQL Server and MySQL.

Krishnraj Rana · Accepted Answer · 2014-10-09 10:22:09Z

1

Try this -

SELECT SalesId, CustomerId, Amount, 
SalesNum = ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY SalesId) 
FROM YOURTABLE

answered Oct 9, 2014 at 10:22

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

1 Comment

Joachim Isaksson Over a year ago

To quote the question; "I am on MS SQL but I am also interested in the MYSQL solution should I need to do this there in the future."

Collectives™ on Stack Overflow

SQL Server: how to create sequence number column

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related