OraFAQ Forum: SQL & PL/SQL » Selecting top records

Home » SQL & PL/SQL » SQL & PL/SQL » Selecting top records (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Selecting top records [message #606752]

Tue, 28 January 2014 15:45

KeithS
Messages: 3
Registered: February 2012

Junior Member

Hello,
I searched through the Top records post, but did not find an answer and am hopeful someone can provide some help.
I have a table called Progress_Notes. There are 3 fields in it I need to use for a report, note_nbr (primary key), client_nbr, service_date.
The note_nbr is obviously system assigned. Each client could have multiple records. I want to select the 2 note_nbrs for each client_nbr and the service_date associated with the record that would be the last 2 based on the system assigned note_nbr.
Example:

note_nbr client_nbr service_date
10 4441 1/1/2013
11 4441 1/10/2013
12 4441 1/15/2013
13 4441 1/20/2013
14 5552 1/25/2013
15 5552 1/27/2013
16 5552 1/30/2013

I would want the results to be
note_nbr client_nbr service_date
12 4441 1/15/2013
13 4441 1/20/2013
15 5552 1/27/2013
16 5552 1/30/2013

Any help on developing the query to get these results would be of great help.

Thanks.

Report message to a moderator

Re: Selecting top records [message #606754 is a reply to message #606752]

Tue, 28 January 2014 16:28

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

More or less, everything you need is written here: How does one select the TOP N rows from a table?. You'll need to read on RANK analytic function, as you'll need to include the PARTITION BY clause (as you have multiple clients here, and you want to fetch two records per each of them).

Try and come back if you don't succeed. In that case, post query you managed to write and the output you got. Someone will assist if necessary.

Report message to a moderator

Re: Selecting top records [message #606804 is a reply to message #606754]

Wed, 29 January 2014 08:43

KeithS
Messages: 3
Registered: February 2012

Junior Member

Thank you Littlefoot. I was able to get this to work with your help and the link you provided. Below is my code, if it would help anyone else.

SELECT note_nbr, 
       client_nbr, 
       service_date 
FROM   (SELECT note_nbr, 
               client_nbr, 
               service_date, 
               Rank() 
                 over ( 
                   PARTITION BY client_nbr 
                   ORDER BY note_nbr DESC) note_rank 
        FROM   progress_notes) 
WHERE  note_rank <= 2

*BlackSwan formatted code & added {code} tags. Please do so yourself in the future.

[Updated on: Wed, 29 January 2014 08:58] by Moderator

Report message to a moderator

Re: Selecting top records [message #606806 is a reply to message #606804]

Wed, 29 January 2014 09:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Previous Topic:	Restrictions in accessing collections values
Next Topic:	Can you any one help me to solve the unique constraint error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:51:38 CDT 2024