| 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: | Whole Thread | Raw Message | 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 |