From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Window function trouble |
Date: | 2009-12-10 17:00:08 |
Message-ID: | pud42mahnr.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Harald Fuchs | 2009-12-10 17:49:57 | Re: Window function trouble |
Previous Message | A. Kretschmer | 2009-12-09 13:46:23 | Re: SQL state 58P01 triggered by a database script execution |