Thread: Sorting by NULL values

I am doing a query where I need to sort by a column that may be NULL 
because it is coming from an OUTER JOIN.  I noticed a difference between
PostgreSQL and other databases about where NULLs show up.  It seems that 
with Postgres, NULLs are sorted after other values.  Other databases 
sort them before.

Is there any standard on how sorting NULLs work?  Is there a way to 
change Postgres's behavior?  Is there a way to replace the NULLs with 
empty strings?

This is my query:

SELECT product_id
FROM product p
LEFT OUTER JOIN attribute_data ad
ON p.product_id= ad.product_id
AND ad.attribute_id = ?
WHERE p.category_id = ?
ORDER BY data;

The tables look like:

CREATE TABLE product (   product_id serial NOT NULL PRIMARY KEY,   category_id integer NOT NULL
);

CREATE TABLE attribute_data (   attribute_id integer NOT NULL,   product_id integer NOT NULL,   data varchar(1024),
PRIMARYKEY (attribute_id, product_id)
 
);
 - Ian

On Tue, 4 Mar 2003, Ian Burrell wrote:

> Is there any standard on how sorting NULLs work?  Is there a way to
> change Postgres's behavior?  Is there a way to replace the NULLs with
> empty strings?

I think what you want may be either COALESCE or ISNULL/IFNULL or perhaps a
CASE statement.

Ian Burrell wrote:
> I am doing a query where I need to sort by a column that may be NULL 
> because it is coming from an OUTER JOIN.  I noticed a difference between
> PostgreSQL and other databases about where NULLs show up.  It seems that 
> with Postgres, NULLs are sorted after other values.  Other databases 
> sort them before.
> 
> Is there any standard on how sorting NULLs work?  Is there a way to 
> change Postgres's behavior?  Is there a way to replace the NULLs with 
> empty strings?
order by field is null asc/desc, field asc/desc

Regards,
Tomasz Myrta

On Tue, 4 Mar 2003, Ian Burrell wrote:

> I am doing a query where I need to sort by a column that may be NULL
> because it is coming from an OUTER JOIN.  I noticed a difference between
> PostgreSQL and other databases about where NULLs show up.  It seems that
> with Postgres, NULLs are sorted after other values.  Other databases
> sort them before.

> Is there any standard on how sorting NULLs work?  Is there a way to

IIRC, they're either considered greater than or less than non-NULL values,
but the decision is up to the implementation.

> change Postgres's behavior?  Is there a way to replace the NULLs with
> empty strings?

Coalesce should work.

Ian,

> I am doing a query where I need to sort by a column that may be NULL
> because it is coming from an OUTER JOIN.  I noticed a difference between
> PostgreSQL and other databases about where NULLs show up.  It seems that
> with Postgres, NULLs are sorted after other values.  Other databases
> sort them before.

Not all other databases.   It really depends.

> Is there a way to replace the NULLs with
> empty strings?

Sure: UPDATE table SET column = '' WHERE column IS NULL

--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco

On Tue, 2003-03-04 at 15:13, Stephan Szabo wrote:
> On Tue, 4 Mar 2003, Ian Burrell wrote:
>
> > I am doing a query where I need to sort by a column that may be NULL
> > because it is coming from an OUTER JOIN.  I noticed a difference between
> > PostgreSQL and other databases about where NULLs show up.  It seems that
> > with Postgres, NULLs are sorted after other values.  Other databases
> > sort them before.
>
> > Is there any standard on how sorting NULLs work?  Is there a way to

If you care, order by their boolean equivelent first:

order by field is null desc, field

DESC puts nulls first, since true > false

> IIRC, they're either considered greater than or less than non-NULL values,
> but the decision is up to the implementation.
>
> > change Postgres's behavior?  Is there a way to replace the NULLs with
> > empty strings?
>
> Coalesce should work.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc