|A custom ORDER BY clause - Assistance Needed|
In our table we have a PRICE column. Some of the entries have no price, therefore a NULL is inserted. When I attempt to do an ORDER BY PRICE ASC, the results and sort are correct, entries are displayed starting from the lowest price working to the highest price and the entries with the NULL price are displayed last.
However, when I do the opposite: ORDER BY PRICE DESC, the entries with the NULL price value are displayed first, followed by the highest price items working downward to the lowest price.
What would be the proper way or SQL syntax to ALWAYS display items with NULL prices at the end?
Instead of NULL for items without prices should I instead use some other value ($0.00 doesn’t work either)?
The column type is MONEY.
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.
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.
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.
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.
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.
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!
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.