I have a dataset that looks like this:
| Car type | date | car |
|---|---|---|
| Honda | May 28, 2022 | Car 1 |
| Honda | May 28, 2022 | Car 1 |
| Honda | August 11, 2022 | Car 2 |
| Honda | August 11, 2022 | Car 2 |
| BMW | May 28, 2022 | Car 1 |
| BMW | May 28, 2022 | Car 1 |
| BMW | August 11, 2022 | Car 2 |
| BMW | August 11, 2022 | Car 2 |
I want to create an additional column "expected" using the row_number(), rank() functions like this:
| Car type | date | car | expected |
|---|---|---|---|
| Honda | May 28, 2022 | Car 1 | 1 |
| Honda | May 28, 2022 | Car 1 | 1 |
| Honda | August 11, 2022 | Car 2 | 2 |
| Honda | August 11, 2022 | Car 2 | 2 |
| BMW | May 28, 2022 | Car 1 | 1 |
| BMW | May 28, 2022 | Car 1 | 1 |
| BMW | August 11, 2022 | Car 2 | 2 |
| BMW | August 11, 2022 | Car 2 | 2 |
I tried the following query. But it doesn't seem to be giving me the correct results
Select car type, date, car,
ROW_NUMBER() OVER (PARTITION BY CarType, Car ORDER BY Date ASC) AS RW from table
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
SELECT CarType, Date, Car, DENSE_RANK() OVER (PARTITION BY CarType ORDER BY Car) AS RW from table