Clash Royale CLAN TAG#URR8PPP

SQL query causes error “Incorrect syntax near the keyword 'order'”

This is my SQL query, and I get an error:

Incorrect syntax near the keyword 'order'

I have checked everything, but unable to correct this.please help

SELECT TOP (1)
CONVERT(VARCHAR(19), DayCheckOut, 120)
FROM
(SELECT TOP (2)
A1.DayCheckOut AS DayCheckOut
FROM
EmployeeAttendace A1
INNER JOIN
EmployeeMaster B1 ON A1.EmployeeId = B1.Id
WHERE
B1.EmailId = 'raja.xyz@gmail.com'
ORDER BY
A1.Id DESC)
ORDER BY
DayCheckOut DESC


...) dt order by DayCheckOut desc

5 Answers
5

Give alias name

SELECT top 1
convert(varchar(19),DayCheckOut,120)
FROM
(SELECT top 2 A1.DayCheckOut as DayCheckOut FROM EmployeeAttendace A1 INNER JOIN
EmployeeMaster B1 ON A1.EmployeeId = B1.Id WHERE B1.EmailId =
'pooja.yadav@computronics.in' order by A1.Id desc) as AliasName
order by DayCheckOut desc


You need an alias, let's say q

q

for the subquery (SELECT top 2 A1.DayCheckOut as DayC ... order by A1.Id desc) q

(SELECT top 2 A1.DayCheckOut as DayC ... order by A1.Id desc) q

that's very common problem for MS SQL Server DB.

No need for that sub-query, modern SQL Server versions support OFFSET/FETCH FIRST.

OFFSET

FETCH FIRST

SELECT convert(varchar(19),A1.DayCheckOut,120)
FROM EmployeeAttendace A1 INNER JOIN EmployeeMaster B1 ON A1.EmployeeId = B1.Id
WHERE B1.EmailId = 'raja.xyz@gmail.com'
order by A1.Id desc
offset 1 fetch next 1 row only


I.e. use OFFSET to skip the first row, use FETCH NEXT to return only 1 row.

OFFSET

FETCH NEXT

It errors there, because you need an alias for a subquery.

However, you didn't need the redundant subquery:

SELECT top (1) convert(varchar(19),A1.DayCheckOut,120) as DayCheckOut
FROM EmployeeAttendace A1
INNER JOIN EmployeeMaster B1 ON A1.EmployeeId = B1.Id
WHERE B1.EmailId = 'raja.xyz@gmail.com'
order by A1.DayCheckOut desc;


SELECT TOP (1) convert(varchar(19),DayCheckOut,120)
FROM (SELECT TOP (2) A1.DayCheckOut as DayCheckOut
FROM EmployeeAttendace A1 INNER JOIN
EmployeeMaster B1
ON A1.EmployeeId = B1.Id
WHERE B1.EmailId in ('raja.xyz@gmail.com')

ORDER BY A1.Id desc
)
ORDER BY DayCheckOut DESC

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.