A custom ORDER BY clause - Assistance Needed - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

A custom ORDER BY clause - Assistance Needed

blaketar

6:30 pm on Mar 24, 2008 (gmt 0)

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.

Demaestro

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

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

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

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

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

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

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.