Select rows based on criteria from within group

Question

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

what if there are 2 rows for a given ass_pos_id that have the same earliest entry date? — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 14, 2016 at 16:09
hi. The nature of the application that uses this data ensures that this scenario will never happen. ie: this is taken care of outside of the database. thanks. — darkpool
– darkpool, Commented Apr 14, 2016 at 16:15

Mureinik · Accepted Answer · 2016-04-14 16:10:53Z

1

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

answered Apr 14, 2016 at 16:10

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select rows based on criteria from within group

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related