Improve company productivity with a Business Account.Sign Up

x
?
Solved

get the first record in qualified group in oracle

Posted on 2009-04-01
4
Medium Priority
?
1,397 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

Join & Write a Comment

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…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

595 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