Display Value From Another Row

Question

I have to write a query to display the value for ID2 when ID=ID. Currently the table looks like the below.

ID	ID2	fname	lname	address1	address2	city	state
123	123	joe	smith	12 main st		los angeles	CA
122	122	james	jones	13 main st		new york	NY
123	3210	joe	smith	14 main st		los angeles	CA
124	124	mary	jones	15 main st		new york	NY

The desired output would look like this. Where I can do some sort of a self join to get the ID2 value.

ID	ID2	fname	lname	address1	address2	city	state	other ID
123	123	joe	smith	12 main st		los angeles	CA	3210
122	122	james	jones	13 main st		new york	NY
124	124	mary	jones	15 main st		new york	NY

Any ideas/suggestions greatly appreciated!

Update:

I was able to get the result with the additional columns using the below script. Thanks all for your assistance on this one!

with cte (id, id2, fname, lname, address1, address2, city, state)
as (select *
from (select *,row_number() over(partition by id order by id desc) as rn from your_table
) x
where rn = 2)
select x.id, x.id2, x.fname, x.lname, x.address1, x.address2, x.city, x.state, c.id2, c.address1
from (select *,row_number() over(partition by id order by id desc) as rn from your_table
) x
left join
cte c on x.id=c.id
where x.rn = 1

dbfiddle below https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=4010ab08c5e32d9293d10e985adbfd7a

Answer 1

If you have two rows max with the same id, then you can try this :

SELECT (array_agg(t.*) FILTER (WHERE id = id2))[1].*
     , (array_agg(t.id2) FILTER (WHERE id <> id2))[1] AS "other ID"
  FROM your_table AS t
 GROUP BY id

If you may have more than two rows with the same id then you can try this :

SELECT (array_agg(t.*) FILTER (WHERE id = id2))[1].*
     , array_agg(t.id2) FILTER (WHERE id <> id2) AS "other IDs"
  FROM your_table AS t
 GROUP BY id

see the test result in dbfiddle