[SQL] Window function trouble
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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Window function trouble
In article , Harald Fuchs writes: > The other users for one address are gone. Does anyone know why? Update: they are not gone, but they've moved to the end of the result set. Apparently the CASE expression is named "adr" unless named otherwise, and the result set is sorted by this expression instead of the column named "adr". Does anyone know what the SQL standard says about that? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Window function trouble
Harald Fuchs writes: > Apparently the CASE expression is named "adr" unless named > otherwise, and the result set is sorted by this expression instead of > the column named "adr". Does anyone know what the SQL standard says > about that? The SQL standard says the default name for any output column other than a simple column reference is implementation-dependent. I think our implementation involves looking at the default value for a CASE. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Window function trouble
In article <25983.1260468...@sss.pgh.pa.us>, Tom Lane writes: > The SQL standard says the default name for any output column other > than a simple column reference is implementation-dependent. I think > our implementation involves looking at the default value for a CASE. Thanks for the clarification, Tom - so it's not a PostgreSQL bug, but a misfeature of the SQL standard. That being said, I still think that PostgreSQL could do better - how about naming expression columns so that they are distinct from column names? The current implementation throws an error e.g. for SELECT adr, CASE lag(adr) OVER (ORDER BY adr) WHEN adr THEN NULL ELSE adr END, usr, count(*) FROM tbl WHERE host = 'h1' GROUP BY adr, usr ORDER BY adr, usr namely `ORDER BY "adr" is ambiguous' which I find somewhat confusing. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Window function trouble
Harald Fuchs writes: > That being said, I still think that PostgreSQL could do better - how > about naming expression columns so that they are distinct from column > names? Even though the rules we use are pretty arbitrary, I'm hesitant to make changes in them; it seems more likely to break existing applications than to do anyone any good. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
