Click here to Skip to main content
15,901,205 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Everytime I run this SQL it says
-syntax error (missing operator) in query expression 'row_number() over (order by RunTime)'

Table Name:EventsDetail
Columns: MemberID, EventNumber, RUnTime

adoqueryAdmin.SQL.Text:=Select EventNumber,MemberID, RunTime, ROW_NUMBER() OVER (ORDER BY RunTime) AS Place from EventsDetail where EventNumber=''AAA001'' order by RunTime'

How do I solve it?
JR009 6-Oct-14 3:38am    
I think, you are using order by clause two times in query that's why error occurs.
Try to remove order clause from last in query then run it.
Schatak 6-Oct-14 3:51am    
by writing order by two times doesn't cause this issue.
Issue can be with your syntax in writing colon signs. can you please share your code how you are writing?
Herman<T>.Instance 6-Oct-14 6:26am    
have you tried:
Select EventNumber,MemberID, RunTime, ROW_NUMBER() OVER (PARTITION BY runtime ORDER BY RunTime) AS Place from EventsDetail where EventNumber=''AAA001'' ?
Kornfeld Eliyahu Peter 6-Oct-14 6:57am    
What version of SQL do you using?
Maciej Los 6-Oct-14 13:53pm    
Have a look at the beginning of statement. You missed (') before SELECT.

1 solution

try like this

declare @Test Table
id int identity(1,1),
number int,
runtime time
insert into @Test values(1,'17:32:19')
insert into @Test values(2,'17:33:22')
insert into @Test values(3,'17:32:20')

Select Number,ID, runtime, ROW_NUMBER() OVER (ORDER BY runtime) AS Place
 from @TEST where Number='1' order by runtime

its working fine.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900