homepage Welcome to WebmasterWorld Guest from 54.205.241.107
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
A custom ORDER BY clause - Assistance Needed
blaketar

10+ Year Member



 
Msg#: 3609344 posted 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

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3609344 posted 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

10+ Year Member



 
Msg#: 3609344 posted 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

WebmasterWorld Senior Member vincevincevince us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3609344 posted 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

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3609344 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3609344 posted 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

10+ Year Member



 
Msg#: 3609344 posted 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

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3609344 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved