Home » SQL & PL/SQL » SQL & PL/SQL » Selecting details from the 3 most recent rows
Selecting details from the 3 most recent rows [message #256220] Fri, 03 August 2007 08:03 Go to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Oracle 10

I want to select the 3 most recent DATEs with AMOUNTS for each NAME from a table that contains monthly data for the last 3 years.

The data looks something like this:

NAME DATE AMOUNT
Smith 05/2007 100
Smith 06/2007 200
Smith 07/2007 300
Smith 08/2007 400
etc

So my results would be:

NAME DATE AMOUNT
Smith 06/2007 200
Smith 07/2007 300
Smith 08/2007 400
etc


Thought I could be able to do a sub query, smething like below, but can't see how to do the count at Name level, as opposed to a count overall, which I think is what my code is doing.

Please can someone help get me started.

select NAME, DATE, AMOUNT
from TABLE1 a, NAMETABLE
where
(select count(*) from TABLE3 b
where a.DATE > b.DATE) <4

and NAMETABLE.NAME = a.NAME


Re: Selecting details from the 3 most recent rows [message #256225 is a reply to message #256220] Fri, 03 August 2007 08:14 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have a look at the RANK, DENSE_RANK and ROW_NUMBER analytic functions. You might also want to search for the topic 'Top N' as this is a very common question.
Previous Topic: group by clause
Next Topic: Outher Join Problem
Goto Forum:
  


Current Time: Tue Dec 06 10:35:13 CST 2016

Total time taken to generate the page: 0.11095 seconds