How do I get a sub-query to just take the first row 2

[RottPaws]

I have a script that ranks order contacts. 99.99% of the time, this results in a single top contact that I use to update a table.

But that one in 10k that has 2 contacts with the same ranking kills my script with the "single-row subquery returns more than one row" error .

Here's my query:

select
	order_id,
	contact_nm,
	email_addr_txt,
	contact_type_cd
from
	completed_contacts cc
where
	sc.pri=(select min(sc1.pri)
		   from completed_contacts cc1
		   where cc1.order_id=cc.order_id
		   group by cc1.order_id)

Is there way to make a sub-query just take the first row when there are multiple rows returned?



_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

Sure, Rott, I would just throw in another subquery that breaks the tie...try this:

select
    order_id,
    contact_nm,
    email_addr_txt,
    contact_type_cd
from
    completed_contacts cc
where
    rowid = (select min(rowid)
               from completed_contacts 
              where sc.pri=
               (select min(sc1.pri)
                  from completed_contacts cc1
                 where cc1.order_id=cc.order_id
                 group by cc1.order_id)
             );

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

SELECT order_id,
       contact_nm,
       email_addr_txt,
       contact_type_cd
  FROM (SELECT order_id,
               contact_nm,
               email_addr_txt,
               contact_type_cd,
               row_number() over(PARTITION BY order_id ORDER BY pri) AS rn
          FROM completed_contacts cc)
 WHERE rn = 1

Regards, Dima

http://seminihin.narod.ru

 

[RottPaws]

Mufasa,

Thank you very much! I couldn't get the results I wanted from the query you gave me, but it put me on the right track to figure it out.

What you gave me only returned one row. What I need is one row per order. In the current case, I have a table with 211 rows representing 126 distinct orders. My original query was retrieving 127 rows. Thanks you your help, I now get 126 rows with the following query:

select
    cc.order_id,
    contact_nm,
    email_addr_txt,
    contact_type_cd
from
    (select
        cc1.*,
        rowid as row_id
     from
        completed_contacts cc1
     where
        cc1.pri=(select
                    min(cc2.pri)
               from
                    completed_contacts cc2
               where
                    cc2.order_id=cc1.order_id
               group by
                    cc2.order_id)) cc,
    (select
        order_id,
        min(rowid) row_id
     from
        completed_contacts
     group by
        order_id,pri) tb -- tie-breaker
where
    cc.row_id = tb.row_id

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[RottPaws]

Dima,

I've never heard of PARTITION BY or over(). Very interesting . . . I'll be looking for more information on those.

You query is much more concise.

Thank you!

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[sem]

In general it's also more efficient (needs to access the table only once) but works only (if I remember) in 8i EE, 9i and up, because is based on analytic functions. You may read more about AF in Oracle manuals (SQL Reference).

Regards, Dima

http://seminihin.narod.ru

 

[RottPaws]

Dima,

Yes, your query was noticeably faster than mine. Thanks again.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[avjoshi]

You can also do,

select
    order_id,
    contact_nm,
    email_addr_txt,
    contact_type_cd
from
    completed_contacts cc
where
    sc.pri=(select distinct min(sc1.pri)
           from completed_contacts cc1
           where cc1.order_id=cc.order_id
           group by cc1.order_id)

I don't know if it will be any better/ faster than dima's solution though.

Anand

 

[RottPaws]

You are correct. That would eliminate the error I was receiving, but it wouldn't actually solve the problem I was having.

The problem with that is that there could be 2 or more contacts with the same ranking/priority. Even if I used DISTINCT in the sub query, I could still end up with multiple contacts for a given order.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[maswien]

I might miss the point, I don't understand why you need group by in the sub query, anything wrong with following query:

select
    order_id,
    contact_nm,
    email_addr_txt,
    contact_type_cd
from
    completed_contacts cc
where
    sc.pri=(select min(sc1.pri)
           from completed_contacts cc1
           where cc1.order_id=cc.order_id
           )