Window function trouble

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?

Responses

Browse pgsql-sql by date

  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