[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

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;
0
morinia
Asked:
morinia
1 Solution
 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now