From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Window function trouble
Date: Thu, 10 Dec 2009 18:00:08 +0100
Message-ID: <pud42mahnr.fsf@srv.protecting.net> (raw)
I have a table like this:
CREATE TABLE tbl (
host text NOT NULL,
adr ip4 NOT NULL,
usr text NOT NULL
);
(ip4 is from the ip4r contrib module)
and I want the number of entries per address and per user:
SELECT adr, usr, count(*)
FROM tbl
WHERE host = ?
AND adr <<= ?
GROUP BY adr, usr
ORDER BY adr, usr
That's pretty basic stuff and returns something like this:
adr1 usr1_1 cnt1_1
adr1 usr1_2 cnt1_2
adr1 usr1_3 cnt1_3
adr2 usr2_1 cnt2_1
...
But I want the address to be NULL if it's the same as the address of
the previous row. I came up with this:
SELECT CASE lag(adr) OVER (ORDER BY adr)
WHEN adr THEN NULL
ELSE adr
END AS myaddr,
usr, count(*)
FROM tbl
WHERE host = ?
AND adr <<= ?
GROUP BY adr, usr
ORDER BY adr, usr
This returns something like
adr1 usr1_1 cnt1_1
NULL usr1_2 cnt1_2
NULL usr1_3 cnt1_3
adr2 usr2_1 cnt2_1
...
what's exactly what I want. But when I don't name the CASE expression
(i.e. I delete "AS myaddr"), I get the following:
adr1 usr1_1 cnt1_1
adr2 usr2_1 cnt2_1
...
The other users for one address are gone. Does anyone know why?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: Window function trouble
In-Reply-To: <pud42mahnr.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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