CODEWITHSUNDEEP
sqlplsqloracle-sqldeveloper
Krishna Gollapudi
Krishna Gollapudi

Reputation: 1679

Query that looks up the latest N Number rows when given list of single columnID

Here is my table looks like.

UUID        Varchar     Unique id 
CREATED_ON  TIMESTAMP   Instant
Encounter   Varchar     encounter

I need a query that looks up the latest N Number of rows by each encounter. So if there are 3 encounters they each can have up to N Number rows.

I tried to use sub queries but they are not working. I was able to retrieve latest n number of rows if it is a single encounter. But retrieving for each encounter seems very difficult. Any ideas?

Upvotes: 0

Views: 45

Answers (1)

a1ex07
a1ex07

Reputation: 37382

The following should give you what you want:

SELECT a.* FROM 
(
   SELECT UUID, CREATED_ON, Encounter, 
    ROW_NUMBER() OVER(PARTITION BY Encounter ORDER BY CREATED_ON DESC) AS rn
   FROM table1
) a
WHERE a.rn <= :your_number

Upvotes: 1

Related Questions