I have a table that contains dates, a column that holds several book titles an finally a column that contains the sales quantity per title. On any given day, anywhere between 1 and 10 titles can have sales. Maybe 'Title 1' 10 pieces, 'Title 3' 5 pieces and 'Title 4' 15 pieces. On another day, maybe there are sales only for Title 2 and 7 etc.
Now, focusing on those days where more than 1 title was sold, I can select the date, the title and the sales quantity with an [ORDER BY] sales quantity and indeed the result is a table that has an implied top sales per day via the ordering clause. But what if I also need an index per title so that, for example, I can see day by day the position of say Title 3? Is it possible to add to the query result such a simple index?
So what I hope to achieve is a query result as below:
Date | Title | Index | Sales |
---|---|---|---|
15 AUG 2024 | Title 2 | 1 | 125 |
15 AUG 2024 | Title 4 | 2 | 100 |
15 AUG 2024 | Title 5 | 3 | 90 |
15 AUG 2024 | Title 9 | 4 | 50 |
16 AUG 2024 | Title 1 | 1 | 230 |
16 AUG 2024 | Title 2 | 2 | 150 |
16 AUG 2024 | Title 7 | 3 | 100 |
17 AUG 2024 | Title 4 | 1 | 50 |
17 AUG 2024 | Title 9 | 2 | 20 |
etc.
I'm using MS ACCESS with VB.NET and struggled to find a way. Thanks
As I understood you want index per date order by sale of particular book title,
For that you can use window function row_number()
SELECT Date, Title, Sales, ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Sales DESC) as index from {tableName}