pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMaking NULL entries appear first when ORDER BY <field> ASC
10+ messages / 7 participants
[nested] [flat]
* Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-16 00:55 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 4 replies; 10+ messages in thread
From: Andreas Joseph Krogh @ 2005-02-16 00:55 UTC (permalink / raw)
To: pgsql-sql
Hi, I have the following table, with the query below to list entries from it
where start_date IS NOT NULL:
CREATE TABLE onp_crm_activity_log(
id serial PRIMARY KEY,
start_date timestamp,
start_time timestamp,
end_time timestamp,
title varchar NOT NULL
);
SELECT start_date, start_time, end_time, title
FROM onp_crm_activity_log
WHERE start_date IS NOT NULL
ORDER BY start_date ASC, start_time ASC;
start_date | start_time | end_time | title
---------------------+---------------------+---------------------+-----------
2005-02-03 00:00:00 | 2005-02-03 08:00:00 | | Something
2005-02-03 00:00:00 | 2005-02-03 09:00:00 | 2005-02-03 12:00:00 | Something
2005-02-03 00:00:00 | | | Something
Now, as you see, touples with NULL in the "start_time"-field appear "after"
the others. I would like to make all entries where start_time IS NULL apear
*before* all the others. Any idea how to achieve this?
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-16 02:24 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
3 siblings, 0 replies; 10+ messages in thread
From: Rosser Schwarz @ 2005-02-16 02:24 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
while you weren't looking, Andreas Joseph Krogh wrote:
> Any idea how to achieve this?
...
ORDER BY coalesce(start_date, '1900-01-01') ASC
, coalesce(start_time, '1900-01-01') ASC;
/rls
--
:wq
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field>
@ 2005-02-16 02:31 Ragnar Hafstað <gnari@simnet.is>
parent: Andreas Joseph Krogh <andreak@officenet.no>
3 siblings, 0 replies; 10+ messages in thread
From: Ragnar Hafstað @ 2005-02-16 02:31 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
On Wed, 2005-02-16 at 00:55 +0000, Andreas Joseph Krogh wrote:
> SELECT start_date, start_time, end_time, title
> FROM onp_crm_activity_log
> WHERE start_date IS NOT NULL
> ORDER BY start_date ASC, start_time ASC;
>
> start_date | start_time | end_time | title
> ---------------------+---------------------+---------------------+-----------
> 2005-02-03 00:00:00 | 2005-02-03 08:00:00 | | Something
> 2005-02-03 00:00:00 | 2005-02-03 09:00:00 | 2005-02-03 12:00:00 | Something
> 2005-02-03 00:00:00 | | | Something
>
>
> Now, as you see, touples with NULL in the "start_time"-field appear "after"
> the others. I would like to make all entries where start_time IS NULL apear
> *before* all the others. Any idea how to achieve this?
how about ORDER BY start_date ,
COALESCE(start_time,'0001-01-01 00:00:00'::timestamp)
?
gnari
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-16 04:08 Greg Sabino Mullane <greg@turnstep.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
3 siblings, 0 replies; 10+ messages in thread
From: Greg Sabino Mullane @ 2005-02-16 04:08 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Now, as you see, touples with NULL in the "start_time"-field
> appear "after" the others. I would like to make all entries
> where start_time IS NULL apear *before* all the others.
ORDER BY start_date, CASE WHEN start_time IS NULL THEN 0 ELSE 1 END, start_time
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200502152309
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCEseYvJuQZxSWSsgRAlipAJwKAyqAyLbo9hfpoWkz0SOlTY3feACfa+ng
DqNY4DAJ5TeeGQbI+smNilg=
=LRhP
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-16 04:47 Bruno Wolff III <bruno@wolff.to>
parent: Andreas Joseph Krogh <andreak@officenet.no>
3 siblings, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2005-02-16 04:47 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
>
> Now, as you see, touples with NULL in the "start_time"-field appear "after"
> the others. I would like to make all entries where start_time IS NULL apear
> *before* all the others. Any idea how to achieve this?
SELECT start_date, start_time, end_time, title
FROM onp_crm_activity_log
WHERE start_date IS NOT NULL
ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC;
This assumes you want the NULL start times first within a particular
date. Otherwise change the order in the ORDER BY clause.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-16 09:39 Andreas Joseph Krogh <andreak@officenet.no>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 10+ messages in thread
From: Andreas Joseph Krogh @ 2005-02-16 09:39 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote:
> > Now, as you see, touples with NULL in the "start_time"-field appear
> > "after" the others. I would like to make all entries where start_time IS
> > NULL apear *before* all the others. Any idea how to achieve this?
>
> SELECT start_date, start_time, end_time, title
> FROM onp_crm_activity_log
> WHERE start_date IS NOT NULL
> ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC;
>
> This assumes you want the NULL start times first within a particular
> date. Otherwise change the order in the ORDER BY clause.
Thanks! This si, IMO, the cleanest solution as it doesn't involve any
COALESCE.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Hoffsveien 17 | know how to do a thing and to watch |
PO. Box 425 Skøyen | somebody else doing it wrong, without |
0213 Oslo | comment. |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-23 19:54 Thomas F.O'Connell <tfo@sitening.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 2 replies; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-02-23 19:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bruno Wolff III <bruno@wolff.to>
How would one know from the reference material that it is possible to
include IS NOT NULL in an ORDER BY clause?
Similarly, other than the FAQ, I've never been able to tell from the
SELECT documentation why ORDER BY random() works.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Feb 16, 2005, at 3:39 AM, Andreas Joseph Krogh wrote:
> On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote:
>>> Now, as you see, touples with NULL in the "start_time"-field appear
>>> "after" the others. I would like to make all entries where
>>> start_time IS
>>> NULL apear *before* all the others. Any idea how to achieve this?
>>
>> SELECT start_date, start_time, end_time, title
>> FROM onp_crm_activity_log
>> WHERE start_date IS NOT NULL
>> ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC;
>>
>> This assumes you want the NULL start times first within a particular
>> date. Otherwise change the order in the ORDER BY clause.
>
> Thanks! This si, IMO, the cleanest solution as it doesn't involve any
> COALESCE.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field>
@ 2005-02-23 20:12 Ken Johanson <pg-user@kensystem.com>
parent: Thomas F.O'Connell <tfo@sitening.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ken Johanson @ 2005-02-23 20:12 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-sql; Bruno Wolff III <bruno@wolff.to>
Well, for the docs to list every possible conditional-statement for an
order by clause would just about include them all, so be all the more
confusing. Sub queries, IN, aggregate functions, aliases.. the list goes
on and on. I'd say that knowledge (that most conditionals can be used in
an order-by or group-by) should be implicit once a person has a basic
understanding of the language.
Thomas F.O'Connell wrote:
> How would one know from the reference material that it is possible to
> include IS NOT NULL in an ORDER BY clause?
>
> Similarly, other than the FAQ, I've never been able to tell from the
> SELECT documentation why ORDER BY random() works.
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Feb 16, 2005, at 3:39 AM, Andreas Joseph Krogh wrote:
>
>> On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote:
>>
>>>> Now, as you see, touples with NULL in the "start_time"-field appear
>>>> "after" the others. I would like to make all entries where
>>>> start_time IS
>>>> NULL apear *before* all the others. Any idea how to achieve this?
>>>
>>>
>>> SELECT start_date, start_time, end_time, title
>>> FROM onp_crm_activity_log
>>> WHERE start_date IS NOT NULL
>>> ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC;
>>>
>>> This assumes you want the NULL start times first within a particular
>>> date. Otherwise change the order in the ORDER BY clause.
>>
>>
>> Thanks! This si, IMO, the cleanest solution as it doesn't involve any
>> COALESCE.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-23 20:22 Thomas F.O'Connell <tfo@sitening.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-02-23 20:22 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Yup. Got it. Wasn't thinking clearly about what expression meant.
Thanks!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Feb 23, 2005, at 2:33 PM, Bruno Wolff III wrote:
> On Wed, Feb 23, 2005 at 13:54:50 -0600,
> "Thomas F.O'Connell" <tfo@sitening.com> wrote:
>> How would one know from the reference material that it is possible to
>> include IS NOT NULL in an ORDER BY clause?
>>
>> Similarly, other than the FAQ, I've never been able to tell from the
>> SELECT documentation why ORDER BY random() works.
>
> From the SELECT command documentation:
> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
> * | expression [ AS output_name ] [, ...]
> [ FROM from_item [, ...] ]
> [ WHERE condition ]
> [ GROUP BY expression [, ...] ]
> [ HAVING condition [, ...] ]
> [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
> [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
> [ LIMIT { count | ALL } ]
> [ OFFSET start ]
> [ FOR UPDATE [ OF table_name [, ...] ] ]
>
> Notice that for ORDER BY you can supply an expression. That should be
> a big
> clue why you can use IS NOT NULL and random() in an ORDER BY clause.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Making NULL entries appear first when ORDER BY <field> ASC
@ 2005-02-23 20:33 Bruno Wolff III <bruno@wolff.to>
parent: Thomas F.O'Connell <tfo@sitening.com>
1 sibling, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2005-02-23 20:33 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
On Wed, Feb 23, 2005 at 13:54:50 -0600,
"Thomas F.O'Connell" <tfo@sitening.com> wrote:
> How would one know from the reference material that it is possible to
> include IS NOT NULL in an ORDER BY clause?
>
> Similarly, other than the FAQ, I've never been able to tell from the
> SELECT documentation why ORDER BY random() works.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-02-23 20:33 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-16 00:55 Making NULL entries appear first when ORDER BY <field> ASC Andreas Joseph Krogh <andreak@officenet.no>
2005-02-16 02:24 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2005-02-16 02:31 ` Re: Making NULL entries appear first when ORDER BY <field> Ragnar Hafstað <gnari@simnet.is>
2005-02-16 04:08 ` Greg Sabino Mullane <greg@turnstep.com>
2005-02-16 04:47 ` Bruno Wolff III <bruno@wolff.to>
2005-02-16 09:39 ` Andreas Joseph Krogh <andreak@officenet.no>
2005-02-23 19:54 ` Thomas F.O'Connell <tfo@sitening.com>
2005-02-23 20:12 ` Re: Making NULL entries appear first when ORDER BY <field> Ken Johanson <pg-user@kensystem.com>
2005-02-23 20:33 ` Bruno Wolff III <bruno@wolff.to>
2005-02-23 20:22 ` Thomas F.O'Connell <tfo@sitening.com>
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