SQL order by, where to put it?

Question

I have an sql statement which works, but when I want to add Order By clause, then the query stops working.

The query below works fine:

SELECT DISTINCT property.id
              , property.unid
              , property.imported
              , property.userid
              , CONCAT(user.firstname) as username
              , property.url
              , IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic
              , property.bedrooms
              , property.beds
              , type.meta_val as type
              , property.accommodates
              , property.price
              , IFNULL (
                         (SELECT thumbimg 
                            FROM tblpropertyimages 
                           WHERE pid = property.id 
                           LIMIT 1
                         )
                         , 'temp/misc/noimage.png'
                        ) image
              , property.name as propertyname
              , ( SELECT SUM(rating) FROM tblreviews WHERE pid = property.id ) as totalrating
              , ( SELECT COUNT(id) FROM tblreviews WHERE pid = property.id) as countratings
              , location.name as cityname 
           FROM tblproperty as property 
           JOIN tbluser as user 
             ON property.userid = user.id 
           JOIN tblcommon as type 
             ON property.type = type.id 
           LEFT
           JOIN tblpropertyamenities as p_amenities 
             ON property.id = p_amenities.pid 
           JOIN tbllocation as location 
             ON location.id = property.city 
          WHERE property.status = 'Active' 
            AND user.status = 'Active' 
            AND property.price >= 0 
            AND property.price <= 10000  
          LIMIT 9 
         OFFSET 0

However, If i add this line to the end of the statement:

ORDER BY property.price ASC

Then the query stops working, any idea why this ORDER BY clause is causing the error?

Incidentally, you appear to select no columns from tblpropertyamenities, so I can't see why it's included — Strawberry, Feb 03 '17 at 13:11

score 1 · Accepted Answer · edited May 23 '17 at 11:53

You need to put ORDER BY before the LIMIT:

MYSQL - ORDER BY & LIMIT

SELECT DISTINCT 
property.id,property.unid,property.imported,property.userid,
CONCAT(user.firstname) as username,property.url,
IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic,
property.bedrooms,property.beds,type.meta_val as 
type,property.accommodates,property.price,
IFNULL((select thumbimg from tblpropertyimages where pid=property.id 
limit 1),'temp/misc/noimage.png') as image,
property.name as propertyname,(select sum(rating) from tblreviews where         
pid=property.id) as totalrating,
(select count(id) from tblreviews where pid=property.id) as countratings,
location.name as cityname from tblproperty as property join tbluser as 
user on property.userid=user.id 
join tblcommon as type on property.type=type.id 
left join tblpropertyamenities as p_amenities on 
property.id=p_amenities.pid 
join tbllocation as location on location.id=property.city 
WHERE property.status='Active' and user.status='Active' 
and property.price >= 0 and property.price <= 10000
ORDER BY property.price ASC limit 9 offset 0

The query should be something like that, because you first order and then you filter with limit.

I hope that helps!

score 0 · Answer 2 · answered Feb 03 '17 at 13:09

0

Before the LIMIT clause as shown below. Moreover, LIMIT without order by makes no sense as you are bound to get random sequence or order of data

        AND property.price <= 10000  
        ORDER BY ... //Here
      LIMIT 9

answered Feb 03 '17 at 13:09

Rahul

76,197
13
71
125

SQL order by, where to put it?

2 Answers2