CODEWITHSUNDEEP
sqloracle-database
user1476092
user1476092

Reputation: 63

Oracle query to retrieve first 2 processed records

Ref    Ref1  Processed     process_date
----------------------------------------------
123    xxxx     Y          23-03-2017 12:10:00
123    zzzz     N          23-03-2017 12:11:10
123    yyyy     Y          23-03-2017 12:11:10
123    wwww     Y          23-03-2017 12:11:11
121    xxxx     Y          23-03-2017 11:10:00
121    yyyy     N          23-03-2017 11:11:00
121    zzzz     Y          23-03-2017 11:11:10
120    xxxx     Y          23-03-2017 11:00:00

How to retrieve only 2 processed records in above data based on process date and Ref and there are more than 2 records?

Result should be 

123    xxxx  Y  23-03-2017 12:10:00
123    yyyy  Y  23-03-2017 12:11:10
121    xxxx  Y  23-03-2017 11:11:00
121    zzzz  Y  23-03-2017 11:11:10

EDIT :

I want 2 records only if there are 2 or more records with same ref

Thanks!

Upvotes: 0

Views: 60

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

SELECT Ref
    ,Ref1
    ,Processed
    ,process_date
FROM (
    SELECT t.*
        ,row_number() OVER (
            PARTITION BY ref ORDER BY process_date
            ) rn
        ,COUNT(Ref) OVER (PARTITION BY ref) ct
    FROM yourtable t
    WHERE Processed = 'Y'
    )
WHERE rn IN (
        1
        ,2
        )
    AND ct >= 2
ORDER BY REF DESC
    ,process_date;

EDIT : added count check to restrict record counts > 2

Upvotes: 2

Related Questions