Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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', '20-6-19'),
('x3', 'foo', 'abc road', 'khi', '19-6-19'),
('x4', 'harry', 'abc road', 'khi', '17-6-19'),
('x5', 'bar', 'xyz road', 'lhr', '11-6-19'),
('x6', 'foo', 'xyz road', 'lhr', '17-6-19'),
('x7', 'harry', 'mno road', 'khi', '17-6-19') 
;
7 rows affected

SELECT
    *
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


g_id
l_name
street_address
city
last_date
g_id
l_name
street_address
city
last_date


x1
bar
abc road
khi
2019-06-11
x3
foo
abc road
khi
2019-06-19

x1
bar
abc road
khi
2019-06-11
x4
harry
abc road
khi
2019-06-17

x2
bar
abc road
khi
2019-06-20
x3
foo
abc road
khi
2019-06-19

x2
bar
abc road
khi
2019-06-20
x4
harry
abc road
khi
2019-06-17

x3
foo
abc road
khi
2019-06-19
x2
bar
abc road
khi
2019-06-20

x3
foo
abc road
khi
2019-06-19
x4
harry
abc road
khi
2019-06-17

x3
foo
abc road
khi
2019-06-19
x1
bar
abc road
khi
2019-06-11

x4
harry
abc road
khi
2019-06-17
x2
bar
abc road
khi
2019-06-20

x4
harry
abc road
khi
2019-06-17
x3
foo
abc road
khi
2019-06-19

x4
harry
abc road
khi
2019-06-17
x1
bar
abc road
khi
2019-06-11

x5
bar
xyz road
lhr
2019-06-11
x6
foo
xyz road
lhr
2019-06-17

x6
foo
xyz road
lhr
2019-06-17
x5
bar
xyz road
lhr
2019-06-11

x7
harry
mno road
khi
2019-06-17
null
null
null
null
null


SELECT DISTINCT ON (t1.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


g_id
l_name
street_address
city
last_date
g_id
l_name
street_address
city
last_date


x1
bar
abc road
khi
2019-06-11
x3
foo
abc road
khi
2019-06-19

x2
bar
abc road
khi
2019-06-20
x3
foo
abc road
khi
2019-06-19

x3
foo
abc road
khi
2019-06-19
x2
bar
abc road
khi
2019-06-20

x4
harry
abc road
khi
2019-06-17
x2
bar
abc road
khi
2019-06-20

x5
bar
xyz road
lhr
2019-06-11
x6
foo
xyz road
lhr
2019-06-17

x6
foo
xyz road
lhr
2019-06-17
x5
bar
xyz road
lhr
2019-06-11

x7
harry
mno road
khi
2019-06-17
null
null
null
null
null


SELECT DISTINCT ON (t1.g_id) 
    t1.*,
    t2.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


g_id
l_name
street_address
city
last_date
g_id


x1
bar
abc road
khi
2019-06-11
x3

x2
bar
abc road
khi
2019-06-20
x3

x3
foo
abc road
khi
2019-06-19
x2

x4
harry
abc road
khi
2019-06-17
x2

x5
bar
xyz road
lhr
2019-06-11
x6

x6
foo
xyz road
lhr
2019-06-17
x5

x7
harry
mno road
khi
2019-06-17
null


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


g_id
l_name
street_address
city
last_date
g_id


x1
bar
abc road
khi
2019-06-11
x3

x2
bar
abc road
khi
2019-06-20
x3

x3
foo
abc road
khi
2019-06-19
x2

x4
harry
abc road
khi
2019-06-17
x2

x5
bar
xyz road
lhr
2019-06-11
x6

x6
foo
xyz road
lhr
2019-06-17
x5

x7
harry
mno road
khi
2019-06-17
x7

g_id	l_name	street_address	city	last_date	g_id	l_name	street_address	city	last_date
x1	bar	abc road	khi	2019-06-11	x3	foo	abc road	khi	2019-06-19
x1	bar	abc road	khi	2019-06-11	x4	harry	abc road	khi	2019-06-17
x2	bar	abc road	khi	2019-06-20	x3	foo	abc road	khi	2019-06-19
x2	bar	abc road	khi	2019-06-20	x4	harry	abc road	khi	2019-06-17
x3	foo	abc road	khi	2019-06-19	x2	bar	abc road	khi	2019-06-20
x3	foo	abc road	khi	2019-06-19	x4	harry	abc road	khi	2019-06-17
x3	foo	abc road	khi	2019-06-19	x1	bar	abc road	khi	2019-06-11
x4	harry	abc road	khi	2019-06-17	x2	bar	abc road	khi	2019-06-20
x4	harry	abc road	khi	2019-06-17	x3	foo	abc road	khi	2019-06-19
x4	harry	abc road	khi	2019-06-17	x1	bar	abc road	khi	2019-06-11
x5	bar	xyz road	lhr	2019-06-11	x6	foo	xyz road	lhr	2019-06-17
x6	foo	xyz road	lhr	2019-06-17	x5	bar	xyz road	lhr	2019-06-11
x7	harry	mno road	khi	2019-06-17	null	null	null	null	null