CODEWITHSUNDEEP
sqlsql-server
user10176456
user10176456

Reputation:

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 I am unable to correct this. How can I fix it?

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 = '[email protected]'
     ORDER BY
         A1.Id DESC)
ORDER BY
    DayCheckOut DESC

Upvotes: 1

Views: 7362

Answers (5)

Komal Sharma
Komal Sharma

Reputation: 36

Use:

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 ('[email protected]')
      ORDER BY A1.Id desc
     )
ORDER BY DayCheckOut DESC

Upvotes: 0

jarlh
jarlh

Reputation: 44696

There isn't any need for that sub-query. Modern SQL Server versions support 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 = '[email protected]'
order by A1.Id desc
offset 1 fetch next 1 row only

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

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You need an alias, let's say q, for the subquery (SELECT top 2 A1.DayCheckOut as DayC ... order by A1.Id desc) q

That's a very common problem for an SQL Server database.

Upvotes: 2

Aakash Singh
Aakash Singh

Reputation: 1062

Use an 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 =
    '[email protected]' order by A1.Id desc) as AliasName
 order by DayCheckOut desc

Upvotes: 3

Cetin Basoz
Cetin Basoz

Reputation: 23797

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 = '[email protected]' 
order by A1.DayCheckOut desc;

Upvotes: 0

Related Questions