Solved

get the first record in qualified group in oracle

Posted on 2009-04-01
4
1,391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

738 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