CODEWITHSUNDEEP
sqlwindow-functionshana
Robert
Robert

Reputation: 61

new column with row number sql

I have data like below with two columns, I need an output with new column shown below

Input -

Name,Date,Value  
Test1,20200901,55  
Test1,20200901,100  
Test1,20200901,150  
Test1,20200805,25  
Test1,20200805,30

Row number is based on data from column - Name and Date

Output,

Name,Date,Value, row_number  
Test1,20200901,55,1  
Test1,20200901,100,1  
Test1,20200901,150,1  
Test1,20200805,25,2  
Test1,20200805,30,2

The query using Partition didn't help

select *, row_number() over (partition by Date) as Rank from Table

Can someone please help here

Thank you very much

Upvotes: 0

Views: 485

Answers (2)

GMB
GMB

Reputation: 222482

Use dense_rank() - and an order by clause:

select t.*, dense_rank() over (order by Date) as rn from mytable t

This gives you a sequential number that starts at 1 on the earliest date value increments without gaps everytime date changes.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You want dense_rank():

select *,
       dense_rank() over (order by Date) as Rank
from Table;

There is something suspicious when you are using partition by without order by (even if the underlying database supports that).

Upvotes: 1

Related Questions