2. PHPRunner General questions
  3. Query Sort question
This topic is locked

Query Sort question

 		4/21/2015 3:41:00 PM
PHPRunner General questions
H
 hernanccs author

Hello guys
I have a table with the following columns/value
id name picture

1 John

2 Kate d_kate.jpg

3 Mike c_mike.jpg

4 Dave

5 Carol b_carol.jpg
what im trying to achieve is list them sorted like this
Carol b_carol.jpg

Kate d_kate.jpg

Mike c_mike.jpg

Dave

John
people with picture first sorted by name, then people without picture sorted by name
i tried to sort then using this

ORDER BY picture DESC, name
but i get the wrong result because it is sorting the list using the actual values of the column picture
Carol b_carol.jpg

Mike c_mike.jpg

Kate d_kate.jpg

Dave

John
Is there any way to do this?
Thanks
C
 		copper21 4/22/2015

hernaccs,
You can try CASE statement.
Insert this into your SELECT statement:
CASE WHEN picture is NULL OR picture = '' THEN 2 ELSE 1 END AS PictureOrder
Your SELECT Statement/Query should look like this:
SELECT

id,

name,

picture,

CASE WHEN picture is NULL OR picture = '' THEN 2 ELSE 1 END AS PictureOrder

FROM YOUR_TABLE

WHERE....(if you have WHERE statement)

ORDER BY PictureOrder, name
Brian
H
 		hernanccs author 4/22/2015



hernaccs,
You can try CASE statement.
Insert this into your SELECT statement:
CASE WHEN picture is NULL OR picture = '' THEN 2 ELSE 1 END AS PictureOrder
Your SELECT Statement/Query should look like this:
SELECT

id,

name,

picture,

CASE WHEN picture is NULL OR picture = '' THEN 2 ELSE 1 END AS PictureOrder

FROM YOUR_TABLE

WHERE....(if you have WHERE statement)

ORDER BY PictureOrder, name
Brian


Hello Brian, thanks for your reply
i tried your suggestion and i get an error when i try to run the query, i also tried remove the second condition on the WHEN CASE

CASE WHEN picture is NULL THEN 2 ELSE 1 END AS PictureOrder
and still get the error.
my exact query is this
SELECT

ID,

name,

picture

FROM staff

ORDER BY picture, name
thanks
H
 		hernanccs author 4/22/2015



Hello Brian, thanks for your reply
i tried your suggestion and i get an error when i try to run the query, i also tried remove the second condition on the WHEN CASE

CASE WHEN picture is NULL THEN 2 ELSE 1 END AS PictureOrder
and still get the error.
my exact query is this
SELECT

ID,

name,

picture

FROM staff

ORDER BY picture, name
thanks


I managed to solve this problem followng your advice, but with a little chance
SELECT ID, name, picture,

CASE picture

WHEN '' THEN 2

ELSE 1

END AS pictureorder

FROM cast_main

ORDER BY pictureorder, name
for some reason if i use NULL or IS NULL instead of '' the query ignore that and assign 1 to everything; i also tried using NOT NULL instead. Also had to made a change on the database to disallow NULL values on that column in the table.
after that i had to remove the new field from the list, edit, view, etc pages on the phprunner project
it would be good to find out why it didnt work with NULL
thanks