Solved

get the first record in qualified group in oracle

Posted on 2009-04-01
4
1,388 Views
Last Modified: 2013-12-18
i have the following data:

A      B      C
Adata1      dataB1      100
Adata1      dataB2      200
Adata1      dataB3      300
Adata2      dataB4      50
Adata2      dataB5      340
Adata2      dataB6      400

What would be the query in order to get the first record for every group where C >= 300

A      B      C
Adata1      dataB3      300
Adata2      dataB5      340

0
Comment
Question by:edyonline
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24044019
this should do:
with data as ( 
  select t.*, row_number() over (partition by A order by C asc ) r
   from yourtable t
   where t.C >= 300
  )
select a,b,c
 from data
where r = 1

Open in new window

0
 

Author Comment

by:edyonline
ID: 24044047
is it possible if i want to get it done in single level of query?
0
 

Author Comment

by:edyonline
ID: 24044048
maybe using dense_rank first command?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24044069
>is it possible if i want to get it done in single level of query?
no, as you cannot use the analytical functions (like row_number) directly in the WHERE clause.
note that oracle is quite clever about these kind of constructs...
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

776 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