Selecting on a condition in window function postgresql

Question

I am using postgresql and applying window function. previously I had to find first gid with same last name , and address(street_address and city) so i simply put last name in partition by clause in window function.

but now I have requirement to find first g_id of which last name is not same. while address is same How can I do it ?

This is what i was doing previously.

SELECT g_id as g_id,
 First_value(g_id) 
 OVER (PARTITION BY lname,street_address , city , 
           order by  last_date DESC NULLS LAST )as c_id,
street_address as street_address  FROM my table;

lets say this is my db

g_id | l_name | street_address | city | last_date
_________________________________________________
x1   | bar    | abc road       | khi  | 11-6-19

x2   | bar    | abc road       | khi  | 12-6-19

x3   | foo    | abc road       | khi  | 19-6-19

x4   | harry  | abc road       | khi  | 17-6-19

x5   | bar    | xyz road       | khi  | 11-6-19

_________________________________________________

In previous scenario : for if i run for the first row my c_id, it should return 'x2' as it considers these rows:

_________________________________________________
g_id | l_name | street_address | city | last_date
_________________________________________________
x1   | bar    | abc road       | khi  | 11-6-19

x2   | bar    | abc road       | khi  | 12-6-19
_________________________________________________

and return a row with latest last_date.

what i want now to select these rows (rows with same street_address and city but no same l_name):

g_id | l_name | street_address | city | last_date
_________________________________________________
x1   | bar    | abc road       | khi  | 11-6-19

x3   | foo    | abc road       | khi  | 19-6-19

x4   | harry  | abc road       | khi  | 17-6-19
_________________________________________________

and output will be x3.

somehow i want to compare last_name column if it is not equals to the current value of last name and then partition by address field. and if no rows satisfy the condition c_id should be equal to current g_id

How is 11-6-19 latest to 12-6-19? Did you mean earliest? — Kaushik Nayak
– Kaushik Nayak, Commented Jul 17, 2019 at 8:10
please note I have used DESC while order by last_day , i want most row with the most recent date. — AQEEL ALTAF
– AQEEL ALTAF, Commented Jul 17, 2019 at 8:30
Is "raod" a typo? If you want to take another street name a more different name would be useful... I am still not sure what you mean with "where last name is not the same"... You sample is not very useful to stress this IMHO — S-Man
– S-Man, Commented Jul 17, 2019 at 9:20
yes raod is a typo let it just be any value. In window function , I want to select rows which do not have same l_name as the current value of iterator' last name but same street_address and city. — AQEEL ALTAF
– AQEEL ALTAF, Commented Jul 17, 2019 at 10:16

S-Man · Accepted Answer · 2019-07-17 12:40:20Z

2

After discussing the details in this chat:

demo:db<>fiddle

SELECT DISTINCT ON (t1.g_id) 
    t1.*,
    COALESCE(t2.g_id, t1.g_id) AS g_id
FROM
    mytable t1
    LEFT JOIN mytable t2
    ON t1.street_address = t2.street_address AND t1.l_name != t2.l_name
ORDER BY t1.g_id, t2.last_date DESC

answered Jul 17, 2019 at 12:40

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Comments

Kaushik Nayak · Accepted Answer · 2019-07-17 08:22:16Z

1

Looking at your expected output,it's not clear whether you want earliest or oldest for each group. You may change the ORDER BY accordingly for last_date in this query which uses DISTINCT ON

SELECT DISTINCT ON ( street_address, city, l_name) * 
   FROM   mytable 
ORDER  BY street_address, 
          city, 
          l_name, 
          last_date  --change this to last_date desc if you want latest

DEMO

answered Jul 17, 2019 at 8:22

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

4 Comments

AQEEL ALTAF Over a year ago

how does this includes the part where I just want to consider rows which last_name is not equal to current row's last_name.

Kaushik Nayak Over a year ago

@AQEELALTAF : l_name is part of DISTINCT ON

S-Man Over a year ago

Because under the first conditions the answer was quite correct, an upvote is necessary!

Kaushik Nayak Over a year ago

@S-Man : Thank you! We find very few questions in SO which have enough information beforehand, unless seriously demanded.

AQEEL ALTAF · Accepted Answer · 2019-07-17 14:11:57Z

here is how I solved it using subquery creating example table.

CREATE TABLE mytable
("g_id" varchar(2), "l_name" varchar(5), "street_address" varchar(8), "city" varchar(3), "last_date" date)

;

INSERT INTO mytable
("g_id", "l_name", "street_address", "city", "last_date")
VALUES
('x1', 'bar', 'abc road', 'khi', '11-6-19'),
('x2', 'bar', 'abc road', 'khi', '12-6-19'),
('x3', 'foo', 'abc road', 'khi', '19-6-19'),
('x4', 'harry', 'abc road', 'khi', '17-6-19'),
('x5', 'bar', 'xyz road', 'khi', '11-6-19')

;

query to get g_ids

SELECT * ,
(select b.g_id from mytable b where (base.g_id = b.g_id) or (base.l_name <> 
b.l_name and base.street_address = b.street_address and base.city = b.city ) 
order by b.last_date desc  limit 1)
from mytable base

Collectives™ on Stack Overflow

Selecting on a condition in window function postgresql

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related