0

Possible Duplicate:
MySQL Orderby a number, Nulls last

I need to order results in the following way using MS SQL. It needs to order the results by name first alphabetically and then by image name thats not null. And all rows where image is null need to show last and all rows where the image is not null should go first ordered alphabetically by name.

I'm calling the MS Sql query from PHP and it looks similar to this

$sql = "SELECT * FROM products ORDER BY name AsC, image DESC";

Is this possible and how?

Community
  • 1
  • 1
Elitmiar
  • 35,072
  • 73
  • 180
  • 229
  • possible duplicate of [MySQL Orderby a number, Nulls last](http://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last) or [SQL Server ORDER BY date and nulls last](http://stackoverflow.com/questions/5886857/sql-server-order-by-date-and-nulls-last) – hakre Nov 23 '12 at 09:49

1 Answers1

3

You can use CASE

SELECT * FROM products 
ORDER BY 
   name ASC, 
   CASE WHEN image IS NULL THEN 1 ELSE 0 END ASC,
   image ASC
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 1
    I think the order should be `case`, `name` then `image` (the `case` first because "all rows where image is null need to show last") – CyberDude Nov 23 '12 at 09:47
  • @CyberDude: No _"It needs to order the results **by name first** alphabetically and **then by image name thats not null**..."_ He wants the _null-images_ to be last of the second order group. The highest priority has the _name_. – Tim Schmelter Nov 23 '12 at 09:49
  • Yes but the last part of the question suggests that the null images are an exception and they need to be last. It would not make much sense otherwise anyway because the clause on null images would only have a visible effect for names that are the same (which is not so likely). If all names are distinct then the image clause has no effect. A collection of names is likely to "lean" more towards distinct values more often than not. – CyberDude Nov 23 '12 at 09:54
  • @Tim - With a few adjustments this query did it. I did the following and it did the trick ORDER BY CASE WHEN image IS NULL THEN 0 ELSE 1 END DESC,name ASC,image DESC – Elitmiar Nov 23 '12 at 10:05
  • @Roland: `WHEN image IS NULL THEN 0 ELSE 1 END DESC` is the same as my `WHEN image IS NULL THEN 1 ELSE 0 END ASC`. So Cyberdude's assumptiojn was correct. The name has not highest priority in the order but the image. My first understanding was different: order by name first, then by a nullable image where the records with image!=null come first in case of multiple records with the same name. – Tim Schmelter Nov 23 '12 at 10:08
  • @Tim - yes he was, thanx for the answer, it helped me in the right direction – Elitmiar Nov 23 '12 at 13:31