Need help with correct SQL query

Question

So I asked a similar question yesterday, and got a great response - I'm hoping for the same luck this time around. What I'm trying to create is paged results for a MySQL query. I'm taking advantage of LIMIT. Here is my query.

SELECT 
  BookingInfo.ClinicID, BookingInfo.BookingDate, BookingInfo.BookingTime, 
  BookingInfo.Status, PatientBooking.FirstName, PatientBooking.LastName, 
  PatientBooking.DateOfBirth 
FROM BookingInfo 
  LEFT JOIN PatientBooking 
    ON BookingInfo.PatientID = PatientBooking.PatientID 
WHERE PatientBooking.FirstName = 'Brian' 
  AND PatientBooking.LastName = 'Lang' 
  AND (BookingInfo.ClinicID = '1' OR BookingInfo.ClinicID = '2') 
LIMIT '0, 20' 
ORDER BY BookingInfo.BookingDate DESC 

This query does not work (However if I remove the LIMIT '0,20' part it works just fine). Instead I get this error returned: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0, 20' ORDER BY BookingInfo.BookingDate DESC' at line 1

Any ideas from the experts?

Answer 1

Your LIMIT clause should not be enclosed in quotes. It should just read LIMIT 0, 20. Quotes are usually reserved for character strings.

Also, the LIMIT clause must appear after the ORDER BY clause. See SheepSimulator's answer for details.

Ref

Answer 2

I think the limit should come after the order. But what about is not working?

Answer 3

Your ORDER clause should be placed before the LIMIT clause.

Check out the MySQL syntax table for SELECT.

Answer 4

1. The LIMIT clause should be after the ORDER BY clause.
2. It's format is LIMIT 0, 20 not LIMIT '0, 20'.

Reference: MySQL SELECT syntax.