Get the row based on the previous date

Hi,

I have table which holds the data like below

NAME          DATE
A                 29/08/2013 12:52:42 AM
B                 11/05/2012 03:29:44 AM
C                 22/08/2013 01:31:19 AM
D                 21/07/2013 03:29:44 AM

Open in new window


I can fetch the latest data based on the date from above result set. But now i need to fetch the data of second date in the list. I mean the previous date of the latest one.

Thanks
Suriyaraj_SudalaiappanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
use of row_number() is usually the way to solve such issues

select *
from (
select name, date, row_number() over (order by date DESC) as row_ref
)
--where row_ref = 1
where row_ref = 2
-- where row_ref < 3

but is there "more" about the query we should know about?
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
just added the from table in the query given by portletpaul :

select *
from (
select name, date, row_number() over (order by date DESC) as row_ref
from your_table_name -- added this here
)
where row_ref = 2
0
Suriyaraj_SudalaiappanAuthor Commented:
Hi Port,

We are doing model publication in oracle configurator and it will store in the table with the publication date. Based on the date we will come to know which one is latest. Here the requirement is to get the earlier publication which is done before the latest. So we end up writing the query to check this.

Thanks
0
PortletPaulfreelancerCommented:
the reason for asking is that - frequently - it is necessary to use "partition by" as well as "order by"

for example, if you needed the second most recent publication for each name

select *
from (
select name, date, row_number() over (partition by name order by date DESC) as row_ref
)
where row_ref = 2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.