CODEWITHSUNDEEP
mysqljoinsql-order-by
Senthil Kumar Bhaskaran
Senthil Kumar Bhaskaran

Reputation: 7451

Order By not working on MySql

Hi Order By not working on MySql

the code is as follows,

select * from School where School.type = 'HighSchool' 
   order by (select locations.name from locations inner join School_locations 
      on locations.id = School_locations.location_id where 
         School_locations.School_id = School.id  and locations.location_country = 'US' limit 1)

and the output is displaying same for both ascending as well as descending how to solve this problem

Upvotes: 0

Views: 1098

Answers (3)

Pradeep Sharma
Pradeep Sharma

Reputation: 35

you can use this query 

 select School.* from School inner join School_locations 
 on School_locations.School_id = School.id 
 inner join locations 
 on locations.id = School_locations.location_id
 where locations.location_country = 'US' and School.type = 'HighSchool' 
 order by locations.name limit 1

Upvotes: 0

nickf
nickf

Reputation: 545995

I don't think you'd need to do the subquery:

SELECT s.*
FROM School s
    INNER JOIN School_locations sl ON (s.id = sl.School_id)
    INNER JOIN locations l ON (l.id = sl.location_id)
WHERE l.location_country = 'US' AND s.type = 'High school'
ORDER BY l.name

Upvotes: 2

Mr. Smith
Mr. Smith

Reputation: 5558

select school.* from school
    inner join school_locations on school_locations.schoolid = school.school_id
    inner join locations on locations.location.id = school_locations.locationid
where
    locations.location_country = 'US' and school.type = 'HighSchool'
order by
    locations.name
limit 1

Upvotes: 0

Related Questions