I have an existing table (call it FruitOrders) with many columns, one of which is fruit:
| Fruit | Order Time |
|---|---|
| Apple | 2022-09-09 22:21:03 |
| Banana | 2022-09-10 22:21:03 |
| Pineapple | 2022-09-11 22:21:03 |
| Orange | 2022-09-12 22:21:03 |
| Apple | 2022-09-13 22:21:03 |
| Orange | 2022-09-14 22:21:03 |
| Blueberry | 2022-09-15 22:21:03 |
I want to add a new column as a view where it will be "1" if it is the first row where the fruit is seen. An example is below:
| Fruit | First Time Seeing Fruit? |
|---|---|
| Apple | 1 |
| Banana | 1 |
| Pineapple | 1 |
| Orange | 1 |
| Apple | 0 |
| Orange | 0 |
| Blueberry | 1 |
Any thoughts on how to do this? I was guessing something involving partition/row_number but I'm new to using these. Also, this is a large table so if there are fast ways to do this let me know!
CodePudding user response：
The window function row_number() within a CASE is an option
Example
Select *
,Flag = case when row_number() over (partition by Fruit order by [Order Time]) = 1 then 1 else 0 end
From YourTable
Order by [Order Time]
Results
