CODEWITHSUNDEEP
sqloracle-databaseoracle10g
john go
john go

Reputation: 1

SQL Select first that matches the criteria

I have a query where in I have to sort the table and retrieve the first value that matches for every id.

The scenario that I would like to achieve is to get the ID of Table A that matches the first ID_2 from the sorted Table B

I have a slight concept of the code.

select A.ID, A.COL1, B.COL1, B.COL2 
  from A, B
 where A.ID = B.ID
   and B.ID_2 = (select ID_2 
                   from (select ID_2 
                           from B B2 
                          where B2.ID = A.ID 
                       order by (case when B2.PRIO ...)) 
                  where rownum = 1)

The problem here is A.ID is not accessible within the select in where clause.

Another way that I found was using analytic function

select ID, COL1, COL2
  from (select A.ID, A.COL1, B.COL2, 
               row_number() over (partition by A.ID order by (case when B.PRIO ...) row_num
          from A, B
         where A.ID = B.ID)
 where row_num = 1

The problem with this code is I think it is not good performance wise.

Can anyone help me? =)

Upvotes: 0

Views: 684

Answers (2)

user5683823
user5683823

Reputation:

You don't need a correlated sub-subquery (which is invalid in Oracle), and you don't need an analytic function either. You need the aggregate first/last function.

... and b.id_2 = (select max(id_2) keep (dense_rank first order by case.....) 
                  from   b b2
                  where  b2.id = a.id
                 )     .....

Even this is probably too complicated. If you would describe your requirement (instead of just posting some incomplete code), the community may be able to help you simplify the query even further.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

row_number() is not a statistic function. It is an analytic or window function. It is probably your best bet. I would do:

select a.*
from A join
     (select b.*, 
             row_number() over (partition by b.ID order by (case when b.PRIO ...) as seqnum
      from b
     ) b
     on A.ID = B.ID and b.seqnum = 1;

If you really only want A.ID, then you don't need A at all . . . the information is in B.ID (assuming it is not being used for filtering). The above then simplifies to:

select b.id
from (select b.*, 
             row_number() over (partition by b.ID order by (case when b.PRIO ...) as seqnum
      from b
     ) b
where b.seqnum = 1;

Upvotes: 1

Related Questions