How select data from two column in sql?

Question

I have a table in postgresql as follow:

id |   name   | parent_id |
1  | morteza  |   null    |
2  |   ali    |   null    |
3  | morteza2 |     1     |
4  | morteza3 |     1     |

My unique data are records with id=1,2, and record id=1 modified twice. now I want to select data with last modified. Query result for above data is as follow:

id |   name   |
1  | morteza3 |
2  |   ali    |

What's the suitable query?

how you think this is latest record – Zaynul Abadin Tuhin Oct 13 '20 at 09:33 — Zaynul Abadin Tuhin, Oct 13 '20 at 09:33

score 3 · Accepted Answer · answered Oct 13 '20 at 09:34

If I am following correctly, you can use distinct on and coalesce():

select distinct on (coalesce(parent_id, id)) coalesce(parent_id, id) as new_id, name
from mytable
order by coalesce(parent_id, id), id desc

Demo on DB Fiddle:

new_id | name    
-----: | :-------
     1 | morteza3
     2 | ali

Erwin Brandstetter · Answer 2 · 2020-10-13T10:15:57.047

From your description it would seem that the latest version of each row has parent_id IS NULL. (And obsoleted row versions have parent_id IS NOT NULL.)

The query is simple then:

SELECT id, name
FROM   tbl
WHERE  parent_id IS NULL;

db<>fiddle here

If you have many updates (hence, many obsoleted row versions), a partial index will help performance a lot:

CREATE INDEX ON tbl(id) WHERE parent_id IS NULL;

The actual index column is mostly irrelevant (unless there are additional requirements). The WHERE clause is the point here, to exclude the many obsoleted rows from the index. See:

How select data from two column in sql?

2 Answers2