Loading

Regarding Over Partition

Regarding Over Partition

Hakan Axheim

5y876SQL

Hi,

I have a question regarding Over(Partition by

This is a short description of my data.

Customer Id Product Id CreateDate

10 A 2020-01-01
10 A 2020-01-01

10 B 2019-11-01

10 B 2019-11-01

20 A 2020-03-01

I need a query that shows the information as below;

Customer Id Product Id CreateDate SequenceNo

10 A 2020-01-01 1
10 A 2020-01-01 1

10 B 2019-11-01 2

10 B 2019-11-01 2

20 A 2020-03-01 1

How can I write a query in SQL that shows the information above?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
The DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records, the rank is decided by the order by parameter passed inside Over , so you are trying to get same rank based on Created Date Column so you should use
select CustomerId, ProductId, CreateDate, DENSE_RANK() OVER (

      ORDER BY CreatedDate

   ) SequenceNo from TableName
+1
Junaid Musla5y ago
Hi,

Check the below query:
select CustomerId, ProductId, CreateDate, DENSE_RANK() OVER (
      ORDER BY Product_Id
   ) SequenceNo from TableName
0
Nisha Regil5y ago
Please try

SELECT Customer_Id, Product_Id, CreateDate
ROW_NUMBER() OVER(PARTITION BY Customer_Id, Product_Id) AS SequenceNo
FROM Table

Ref Link : https://www.sqlshack.com/sql-partition-by-clause-overview/
-1