Skip site navigation (1) Skip section navigation (2)

Re: Group By, Aggregate Functions and NULL

From: "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Group By, Aggregate Functions and NULL
Date: 2006-07-27 18:12:03
Message-ID: 1154023923.713545.144900@i42g2000cwa.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-novice
Brendon Gleeson wrote:
> I need to Group By a date column that may contain a NULL value. Min()  only
> gives me the lowest date and not NULL if it exists. How do I get a Group By
> clause to give me NULL if it exists?

There are a couple of ways to approach this, but it depends on what
exactly you're trying to accomplish, which is not completely clear from
your question. I'll take a shot in the dark and guess that you want all
the NULLs to group together and sort out lower than your other values.
If that's what you want then perhaps a query such as the following
would work for you:

SELECT COALESCE(ts, '-infinity'::timestamp) FROM mytable GROUP BY 1
ORDER BY 1;

For further details please see the following:
http://www.postgresql.org/docs/current/static/functions-conditional.html
http://www.postgresql.org/docs/current/static/datatype-datetime.html

Drew


In response to

pgsql-novice by date

Next:From: Andrew HammondDate: 2006-07-27 18:52:03
Subject: Re: PostgreSQL Replication
Previous:From: Brendon GleesonDate: 2006-07-27 17:25:10
Subject: Group By, Aggregate Functions and NULL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group