Demaestro
msg:3609366 | 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 SELECT price,
CASE WHEN price is null THEN 0.00
END
FROM table
order by price desc; a Coalesce would look like this. SELECT
COALESCE(price, 0.00) as price
from table
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: SELECT
COALESCE(price, 'Price Not Available') as price
from table
order by price desc Because the value 'Price Not Available' is a string not a double or whatever datatype you are using.
|
bmcgee
msg:3609703 | 2:29 am on Mar 25, 2008 (gmt 0) |
SELECT isnull(price, 0.00) as realprice
FROM table
order by realprice desc isnull() will return the 2nd parameter in place of the first, if the first is null.
|
vincevincevince
msg:3609706 | 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.
|
Demaestro
msg:3610012 | 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.
|
LifeinAsia
msg:3610111 | 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.
|
blaketar
msg:3610128 | 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!
|
Demaestro
msg:3610138 | 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.
|
|
