CODEWITHSUNDEEP
sqlpostgresqlselect
darkpool
darkpool

Reputation: 14641

Select rows based on criteria from within group

I have the following table:

pk_positions    ass_pos_id   underlying   entry_date
1               1            abc          2016-03-14
2               1            xyz          2016-03-17
3                            tlt          2016-03-18
4               4            ujf          2016-03-21
5               4            dks          2016-03-23
6               4            dqp          2016-03-26

I need to select one row per ass_pos_id which has the earliest entry_date. Rows which do not have a value for ass_pos_id are not included.

In other words, for each non null ass_pos_id group, select the row which has the earliest entry_date

The following is the desired result:

pk_positions    ass_pos_id   underlying   entry_date
1               1            abc          2016-03-14
4               4            ujf          2016-03-21

Upvotes: 1

Views: 46

Answers (1)

Mureinik
Mureinik

Reputation: 310993

You could use the row_number window function:

SELECT pk_positions, ass_pos_id, underlying, entry_date
FROM   (SELECT pk_positions, ass_pos_id, underlying, entry_date,
               ROW_NUMBER() OVER (PARTITION BY ass_pos_id
                                  ORDER BY entry_date ASC) rn
        FROM   mytable
        WHERE  ass_pos_id IS NOT NULL) t
WHERE   rn = 1

Upvotes: 1

Related Questions