Solved

get the first record in qualified group in oracle

Posted on 2009-04-01
4
1,387 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 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

11 Experts available now in Live!

Get 1:1 Help Now