Обсуждение: ORDER BY TIMESTAMP_column ASC, NULL first

Hi!

I read posts telling me that NULL values are considered greater than
non-null values. Fine. Is there a way to explicitly reverse this?

I have the situation where a table holds IP-addresses. The table has
column of type timestamp, called assignedAt, which tells when the
address was assigned to a computer in our network.
What I'd like to do, is to get an IP-address that hasn't yet been
assigned to anyone, or the one with the smallest assignedAt (most likely
to not be in use).

CREATE TABLE IPv4Address (id        SERIAL PRIMARY KEY,address        INET NOT NULL UNIQUE,assignedAt    TIMESTAMP
);

The query to use would be,
SELECT ip.id FROM IPv4Address ip ORDER BY assignedAt ASC LIMIT 1;

with the exception that this returns rows with NULL at the end, instead
of at the beginning which is what I'd like.

How do achieve this with one query? I'm using Postgres 7.4.

And oh, I'm not on the list so please cc my adress in any replies!


Thanks in advance,
Fredrik Wendt

Dnia 2004-02-12 11:06, Użytkownik Fredrik Wendt napisał:

> Hi!
> 
> I read posts telling me that NULL values are considered greater than
> non-null values. Fine. Is there a way to explicitly reverse this?

Sure, choose combination you need:

order by some_value is null asc/desc, some_value asc/desc

Regards,
Tomasz Myrta

On Thu, 12 Feb 2004, Fredrik Wendt wrote:

> I read posts telling me that NULL values are considered greater than
> non-null values. Fine. Is there a way to explicitly reverse this?

Not directly, but I think ORDER BY assignedAt IS NOT NULL, assignedAt ASC
will give the ordering you want at the cost of probably not using an index
on assignedAt.

On Thu, 2004-02-12 at 05:06, Fredrik Wendt wrote:
> Hi!
> 
> I read posts telling me that NULL values are considered greater than
> non-null values. Fine. Is there a way to explicitly reverse this?

ORDER BY column IS NOT NULL, column ASC;

Hi Rod,

Try this....

ace=> create table test(name text, age int );
CREATE
ace=> insert into test values ('Denis',26);
INSERT 1823531 1
ace=> insert into test values (null,26);
INSERT 1823532 1
ace=> select * from test order by name;name  | age
-------+-----Denis |  26      |  26
(2 rows)

ace=> select * from test order by coalesce(name,'');name  | age
-------+-----      |  26Denis |  26     

HTH

Thanx

Denis

----- Original Message ----- 
From: "Rod Taylor" <pg@rbt.ca>
To: "Fredrik Wendt" <fredrik@csbnet.se>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, February 16, 2004 12:32 AM
Subject: Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first


> On Thu, 2004-02-12 at 05:06, Fredrik Wendt wrote:
> > Hi!
> > 
> > I read posts telling me that NULL values are considered greater than
> > non-null values. Fine. Is there a way to explicitly reverse this?
> 
> ORDER BY column IS NOT NULL, column ASC;
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Thu, Feb 12, 2004 at 11:06:26 +0100, Fredrik Wendt <fredrik@csbnet.se> wrote:
> 
> The query to use would be,
>     
> SELECT ip.id FROM IPv4Address ip ORDER BY assignedAt ASC LIMIT 1;
> 
> with the exception that this returns rows with NULL at the end, instead
> of at the beginning which is what I'd like.
> 
> How do achieve this with one query? I'm using Postgres 7.4.

SELECT ip.id FROM IPv4Address ip ORDER BY assignedAt IS NULL, assignedAt ASC LIMIT 1;