| 7:13 pm on Mar 24, 2008 (gmt 0)|
You can use Coalesce or case for this... Not sure if Coalesce is available but a case would look like
CASE WHEN price is null THEN 0.00
order by price desc;
a Coalesce would look like this.
COALESCE(price, 0.00) as price
order by price desc
Coalesce is meant to substitute null values with a default value. When using it though it must use the same datatype as is used by the table itself.
So you can't go:
COALESCE(price, 'Price Not Available') as price
order by price desc
Because the value 'Price Not Available' is a string not a double or whatever datatype you are using.
| 2:29 am on Mar 25, 2008 (gmt 0)|
SELECT isnull(price, 0.00) as realprice
order by realprice desc
isnull() will return the 2nd parameter in place of the first, if the first is null.
| 2:34 am on Mar 25, 2008 (gmt 0)|
ORDER BY price <> NULL DESC, price ASC
Sort first by whether price is NULL, non-null first, and then within each of those sets, sort by price ASC.
| 2:16 pm on Mar 25, 2008 (gmt 0)|
bmcgee is isnull() a MySql function?
Just to keep things straight for anyone looking this up later. COALESCE(price, 0.00) seems to do the same as isnull(price, 0.00).
COALESCE is available in Postgres and MySql but isnull isn't in Postgres as far as I can tell.
| 3:27 pm on Mar 25, 2008 (gmt 0)|
ISNULL() works on MS SQL. COALESCE() is ANSI standard.
This is good reminder about why it is important to specify which DB you are using when posting questions.
| 3:39 pm on Mar 25, 2008 (gmt 0)|
Found a solution which worked, I should point out that the DB is Postgresql.
As I mentioned, ORDER BY PRICE ASC works correct for sorting from lowest-to-highest with NULL values at the end.
The following clause "ORDER BY (price IS NULL), price DESC" works for sorting highest-to-lowest with NULL values at the end.
Thanks to everyone who replied!
| 3:50 pm on Mar 25, 2008 (gmt 0)|
I haven't seen it done that way. Typically I have used COALESCE(), I wonder now which is more efficient? My gut says the order by statement might be less labor intensive but I can see it both ways.
I will run some explains later if I can and will post back.