2. PHPRunner General questions
  3. Order By in SQL clause
This topic is locked

Order By in SQL clause

 		10/10/2007 8:18:09 AM
PHPRunner General questions
G
 giles author

Hi,
I have a calculated value, Situation, in an SQL query with an Order By that is causing my a problem..
select CompanyName, Status, `Cust Status`, `SVC Status`, Date_Reserv_Sched, Date_ServiceDue,

If(Date_Reserv_Sched is not null,"Scheduled", If(Date_ServiceDue<Now(),"Overdue","Due")) as Situation

from services left join organisations on services.OrgID=organisations.OrgID

Where status="Customer" and `Cust Status`="Program"

order by Situation, Date_ServiceDue
I put the "order by Situation" in the SQL Clause as the field `Situation` does not appear in the list allowed in the "ORDER BY on list page" section and I really need to sort on this calculated field.
Now this query works fine in Mysql Query Browser but produces an error "Unknown column 'Situation' in 'order clause'" when viewing the built pages from phprunner.
Is there a way around this?
J
 		Jane 10/10/2007

Hi,
try to use this query:

select CompanyName, Status, `Cust Status`, `SVC Status`, Date_Reserv_Sched, Date_ServiceDue,

If(Date_Reserv_Sched is not null,"Scheduled", If(Date_ServiceDue<Now(),"Overdue","Due")) as Situation

from services left join organisations on services.OrgID=organisations.OrgID

Where status="Customer" and `Cust Status`="Program"

order by If(Date_Reserv_Sched is not null,"Scheduled", If(Date_ServiceDue<Now(),"Overdue","Due")), Date_ServiceDue
Alexey admin 10/10/2007

Hi,
there is an easier way.

Just specify field number instead of its name in ORDER BY expression

I.e.

select ...

order by 7,6
G
 		giles author 10/10/2007

Thanks Jane and Alexey,
You guys are great with the responses. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=22129&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
I also eventually found that "ORDER BY situation, Date..." works as well. Just didn't realise it could be typed it in at the "ORDER BY on list..." section.