Go Premium for a chance to win a PS4. Enter to Win

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

get the first record in qualified group in oracle

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
edyonline
Asked:
edyonline
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
edyonlineAuthor Commented:
is it possible if i want to get it done in single level of query?
0
 
edyonlineAuthor Commented:
maybe using dense_rank first command?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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