Row_Number Query for Groupby

Question

My query is about to get row number as SN in a SQL query from access database in which I get total sales for the day group by [bill Date] clause

my Working Query is:

sql = "SELECT [Bill Date] as [Date], Sum(Purchase) + Sum(Returns) as [Total Sales] FROM TableName Group By [Bill Date];"

I found this Row_Number Clause over Internet and i tried like this.

sql = "SELECT ROW_NUMBER() OVER (ORDER BY [Bill Date]) AS [SN], [Bill Date] as [Date], Sum(Purchase) + Sum(Returns) as [Total Sales] FROM TableName Group By [Bill Date];"

when i Run the about Code i get this error.

-2147217900 Syntax error (missing operator) in query expression ROW_NUMBER() OVER (ORDER BY [Bill Date]);"

i am using Excel Vba to connect to Access Database Could any one help me to to get it in correct order.

Only see the microsoft examples, always defined ASC or DESC: https://learn.microsoft.com/es-es/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 — deon cagadoes, Aug 03 '19 at 07:52
Possible duplicate of [SQL Row\_Number() not sorting dates](https://stackoverflow.com/questions/43855943/sql-row-number-not-sorting-dates) — deon cagadoes, Aug 03 '19 at 08:13

score 0 · Answer 1 · answered Aug 03 '19 at 07:50

0

Looks like you aren't define DESC or ASC on (ORDER BY [Bill Date]), need be something like this (ORDER BY [Bill Date] DESC) or (ORDER BY [Bill Date] ASC)

answered Aug 03 '19 at 07:50

deon cagadoes

582
2
13

1

Same Error, no improvement. – Raj Shekhar Singh Aug 03 '19 at 07:54
The problem can be too, the ORDER BY datetime, idk if can make order of datetime. An solution can be the correct answer post: https://stackoverflow.com/questions/43855943/sql-row-number-not-sorting-dates/43856455 – deon cagadoes Aug 03 '19 at 08:11

Row_Number Query for Groupby

1 Answers1