Select within a select in Oracle

Experts,

I  have seen the code before where partition by is used in an Oracle Query to get row_num and there is another query concatanated to it to get other columns within the table where row_num = 1. Can someone give me the syntax for this?  I  believe it looks something like this:

Select row_number() over (partition by member_id ORDER BY date_entered DESC) rownum FROM
 Select   member_id
           , claim
          , date_entered
FROM TABLEA
where rownum = 1;
quit;
moriniaAdvanced Analytics AnalystAsked:
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.

MikeOM_DBACommented:
The query you posted makes no sense, try posting your requirements clearly.

Otherwise check: Oracle® Database SQL Language Reference
11g Release 2 (11.2) Analytical Functions
:p
0
johnsoneSenior Oracle DBACommented:
Some sample data and expected results would be helpful.  Looking purely at the query, I would say you are looking for this:

SELECT member_id, 
       claim, 
       date_entered 
FROM   (SELECT member_id, 
               claim, 
               date_entered, 
               Row_number() 
                 over ( 
                   PARTITION BY member_id 
                   ORDER BY date_entered DESC) rn 
        FROM   tablea) 
WHERE  rn = 1; 

Open in new window


However that is a guess.  If that is not what you are looking for, then we need a better understand of what you are trying to accomplish.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.