CODEWITHSUNDEEP
sqloracle-databaseselectoracle11g
user3224907
user3224907

Reputation: 768

Oracle select query to filter rows

Say I have a table with the following values

id (PK) a_num  a_code effect_dt  expire_dt
32      1234   abcd   01/01/2015 05/30/2015
9       1234   abcd   06/01/2015 12/31/2015
5       1234   efgh   01/01/2015 05/30/2015
14      1234   efgh   06/01/2015 12/31/2015

How can I select just one record from a_num,a_code pair. Either Id's 1,3 or 2,4? There may be scenarios where there are more than 2 records for a a_num,a_code pair.

UPDATE - ID will not necessarily always be in order, it is just a primary key.

Upvotes: 1

Views: 1666

Answers (2)

Sagar
Sagar

Reputation: 275

This will give you rows 1 and 3

Select * from (
   Select * , Row_number() Over(Partition by a_num, a_code order by id) r_num from Your_Table ) result
Where r_num = 1

Just use DESC in order by and you will get rows 2 and 4

Select * from (
   Select * , Row_number() Over(Partition by a_num, a_code order by id desc) r_num from Your_Table ) result
Where r_num = 1

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

One way would be to use the row_number window function:

SELECT id, a_num, a_code, effect_dt, expire_dt
FROM   (SELECT id, a_num, a_code, effect_dt, expire_dt,
               ROW_NUMBER() OVER (PARTITION BY a_num, a_code
                                  ORDER BY 1) AS rn
        FROM   mytable) t
WHERE  rn = 1

Upvotes: 1

Related Questions