PostgreSQL, Window function usage question (with specific example)

Question

I have a situation in PostgreSQL that I'm struggling with. The schema/model that I'm working with is not under my control and not something I'm able to alter, so I am trying to figure out the best way to deal with the cards I've been dealt.

First, the schema, simplified for this question, but essentially it's invoice (Type = T) and transaction (Type <> T) lines combined into the same table. There can and will be n-number of tranaction lines per invoice and n-number of invoices per client that can be intermingled as seen below.

Given this schema:

CREATE TABLE t (
id serial PRIMARY KEY,
Type VARCHAR (50) NOT NULL,
InvoiceNo VARCHAR (50) NOT NULL,
ClientId VARCHAR (50) NOT NULL);

and this data:

insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '97', '1');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '2');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '2');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '98', '2');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '99', '1');
insert into t (type, InvoiceNo, ClientId) values ('T', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '100', '1');

which looks like:

Id	Type	InvoiceNo	ClientId
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

the result I'm looking for:

Id	Type	InvoiceNo	ClientId
12	X	100	1
11	S	100	1
10	T	100	1
8	X	99	1
7	S	99	1
5	S	99	1
1	X	97	1
4	X	98	2
3	S	98	2

Playing with an answer from a previous question, I came up with:

select * from (select t.*,
   max(InvoiceNo) filter (where type = 'I') over (partition by clientid order by id DESC) as imputed_invoiceno 
from t) as x
where Type <> 'I';

Which gets me close:

Id	Type	ClientId	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	99
4	X	2	98
3	S	2	98

The only issue here is that record with Id = 1 should have imputed_invoiceno = 97, but doesn't and I'm unclear as to why.

Gordon Linoff · Accepted Answer · 2021-02-15 16:02:32Z

2

Something strange is going on with the string type. You get the expected results with this query using numbers:

select *
from (select t.*,
             min(InvoiceNo) filter (where type = 'I') over (partition by clientid order by id desc) as imputed_invoiceno 
      from t
     ) x
where Type <> 'I';

Note that I changed the max() to a min().

Here is a db<>fiddle.

The issue is that the ordering for strings is not what you expect. It is not 0 < 97 < 98 < 99 < 100. It is '0' < '100' < '97' < '98' < '99'. I think you worked around this using max().

answered Feb 15, 2021 at 16:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL, Window function usage question (with specific example)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Id	Type	InvoiceNo	ClientId
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

Id	Type	ClientId	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	99
4	X	2	98
3	S	2	98

Id	Type	InvoiceNo	ClientId
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

Id	Type	ClientId	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	99
4	X	2	98
3	S	2	98

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Id	Type	InvoiceNo	ClientId
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

Id	Type	ClientId	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	99
4	X	2	98
3	S	2	98