Adding an index to query result

Question

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

Answer 1

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}