Select within a select in Oracle

Posted on 2014-08-19
Last Modified: 2014-08-19

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
where rownum = 1;
Question by:morinia
    LVL 29

    Expert Comment

    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
    LVL 34

    Accepted Solution

    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, 
    FROM   (SELECT member_id, 
                     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.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now