pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY TIMESTAMP_column ASC, NULL first
6+ messages / 6 participants
[nested] [flat]
* ORDER BY TIMESTAMP_column ASC, NULL first
@ 2004-02-12 10:06 Fredrik Wendt <fredrik@csbnet.se>
2004-02-15 19:02 ` Re: ORDER BY TIMESTAMP_column ASC, NULL first Rod Taylor <pg@rbt.ca>
2004-02-15 20:01 ` Re: ORDER BY TIMESTAMP_column ASC, NULL first Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-15 20:11 ` Re: ORDER BY TIMESTAMP_column ASC, NULL first Tomasz Myrta <jasiek@klaster.net>
2004-06-03 17:31 ` Re: ORDER BY TIMESTAMP_column ASC, NULL first Bruno Wolff III <bruno@wolff.to>
0 siblings, 4 replies; 6+ messages in thread
From: Fredrik Wendt @ 2004-02-12 10:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY TIMESTAMP_column ASC, NULL first
2004-02-12 10:06 ORDER BY TIMESTAMP_column ASC, NULL first Fredrik Wendt <fredrik@csbnet.se>
@ 2004-02-15 19:02 ` Rod Taylor <pg@rbt.ca>
2004-02-16 07:57 ` Re: ORDER BY TIMESTAMP_column ASC, NULL first Denis <sqllist@coralindia.com>
3 siblings, 1 reply; 6+ messages in thread
From: Rod Taylor @ 2004-02-15 19:02 UTC (permalink / raw)
To: Fredrik Wendt <fredrik@csbnet.se>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY TIMESTAMP_column ASC, NULL first
2004-02-12 10:06 ORDER BY TIMESTAMP_column ASC, NULL first Fredrik Wendt <fredrik@csbnet.se>
2004-02-15 19:02 ` Re: ORDER BY TIMESTAMP_column ASC, NULL first Rod Taylor <pg@rbt.ca>
@ 2004-02-16 07:57 ` Denis <sqllist@coralindia.com>
0 siblings, 0 replies; 6+ messages in thread
From: Denis @ 2004-02-16 07:57 UTC (permalink / raw)
To: pgsql-sql
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
-------+-----
| 26
Denis | 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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY TIMESTAMP_column ASC, NULL first
2004-02-12 10:06 ORDER BY TIMESTAMP_column ASC, NULL first Fredrik Wendt <fredrik@csbnet.se>
@ 2004-02-15 20:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
3 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2004-02-15 20:01 UTC (permalink / raw)
To: Fredrik Wendt <fredrik@csbnet.se>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY TIMESTAMP_column ASC, NULL first
2004-02-12 10:06 ORDER BY TIMESTAMP_column ASC, NULL first Fredrik Wendt <fredrik@csbnet.se>
@ 2004-02-15 20:11 ` Tomasz Myrta <jasiek@klaster.net>
3 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2004-02-15 20:11 UTC (permalink / raw)
To: Fredrik Wendt <fredrik@csbnet.se>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY TIMESTAMP_column ASC, NULL first
2004-02-12 10:06 ORDER BY TIMESTAMP_column ASC, NULL first Fredrik Wendt <fredrik@csbnet.se>
@ 2004-06-03 17:31 ` Bruno Wolff III <bruno@wolff.to>
3 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2004-06-03 17:31 UTC (permalink / raw)
To: Fredrik Wendt <fredrik@csbnet.se>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-03 17:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-12 10:06 ORDER BY TIMESTAMP_column ASC, NULL first Fredrik Wendt <fredrik@csbnet.se>
2004-02-15 19:02 ` Rod Taylor <pg@rbt.ca>
2004-02-16 07:57 ` Denis <sqllist@coralindia.com>
2004-02-15 20:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-15 20:11 ` Tomasz Myrta <jasiek@klaster.net>
2004-06-03 17:31 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox