Partitioning using multiple fields and getting the same group number per partitioned group

Question

I have a data set that looks like below:

I want to using row_number(), rank() function create an additional column 'Expected' that looks like this:

CarType	Date	Car	Expected
Honda	5/28/2022	car1	1
Honda	5/28/2022	car1	1
Honda	8/11/2022	car2	2
Honda	8/11/2022	car2	2
BMW	5/28/2022	car1	1
BMW	5/28/2022	car1	1
BMW	8/11/2022	car2	2
BMW	8/11/2022	car2	2

I tried a query below. But does not seem to give me the right results

select CarType, Date, Car, ROW_NUMBER() OVER (PARTITION BY CarType, Car ORDER BY Date ASC) AS RW from table

please rearrange your question it is not clear, thanks ! – jmvcollaborator Aug 30 '22 at 01:09 — jmvcollaborator, Aug 30 '22 at 01:09

score 0 · Answer 1 · answered Aug 30 '22 at 06:24

0

SELECT CarType, 
       Date, 
       Car, 
       DENSE_RANK() OVER (PARTITION BY CarType 
                          ORDER BY Car) AS RW 
from table

answered Aug 30 '22 at 06:24

Akina

1 Answers1