CODEWITHSUNDEEP
sqlpostgresqlsql-order-bygreatest-n-per-group
Morteza Malvandi
Morteza Malvandi

Reputation: 1724

How select data from two column in sql?

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?

Upvotes: 0

Views: 76

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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:

Upvotes: 0

GMB
GMB

Reputation: 222402

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

Upvotes: 3

Related Questions