Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

my query is too slow

Jimmie_M
Jimmie_M Member Posts: 150
edited May 2009 in SQL & PL/SQL
DDL-table name(200,000 data) sheet2; Inv_num number(10), Price varchar2(20)
Inv_num	price
9606	X
9613	X
9620	X
9644	X
9668	$34.56 
6401	X
6401	$ 52.32
4720	X
4720	$ 539.03
4737	$ 924.05
4737	X
4782	X
4782	$ 89.40
I want to have the following result(not duplicated record + remove one of duplicated record with price 'x');
Inv_num	price
9606	X
9613	X
9620	X
9644	X
9668	$34.56 
6401	$ 52.32
4720	$ 539.03
4737	$ 924.05
4782	$ 89.40
I did it but it is too slow, would you revise my query thanks
SELECT  Inv_num,price

FROM Sheet2
where Sheet2.Inv_num  in(
SELECT Sheet2.Inv_num  FROM Sheet2 where Sheet2.Inv_num in(
SELECT Sheet2.Inv_numFROM Sheet2
GROUP BY Sheet2.Inv_num HAVING (((Count(Sheet2.Inv_num))>1)))) and Sheet2.price <>'x'
union
SELECT Inv_num,price

FROM Sheet2
where Sheet2.Inv_num not  in(
SELECT Sheet2.Inv_num  FROM Sheet2 where Sheet2.Inv_num in(
SELECT Sheet2.Inv_num FROM Sheet2
GROUP BY Sheet2.Inv_num
HAVING (((Count(Sheet2.Inv_num))>1))))
thanks in advance

Best Answer

  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Accepted Answer
    Assuming that you never have more than one row with an actual price in it (and it begins with a $), wouldn't this do the same thing?
    select inv_num, min(price)
    from my_tab
    group by inv_num;

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    How to post a tuning request:
    865295

    When your query takes too long:
    503834
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Accepted Answer
    Assuming that you never have more than one row with an actual price in it (and it begins with a $), wouldn't this do the same thing?
    select inv_num, min(price)
    from my_tab
    group by inv_num;
  • Jimmie_M
    Jimmie_M Member Posts: 150
    you are so smart. thanks

    ***
    in case, if one invoice number has more than two prices and 'x' in price field.
    I want to keep two prices and delete 'x' record.

    what is the best way? use Union and subquery???
    ***
    if you are busy, it is not neccessary to answer.

    thank you so much
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited May 2009
    I'd do something like:
    with my_tab as (select 9606 inv_num, 'X' price from dual union all
                    select 9606 inv_num, 'X' price from dual union all
                    select 9613 inv_num, 'X' price from dual union all
                    select 9620 inv_num, 'X' price from dual union all
                    select 9644 inv_num, 'X' price from dual union all
                    select 9668 inv_num, '$34.56' price from dual union all
                    select 9401 inv_num, 'X' price from dual union all
                    select 9401 inv_num, '$ 52.32' price from dual union all
                    select 4720 inv_num, 'X' price from dual union all
                    select 4720 inv_num, '$ 539.03' price from dual union all
                    select 4737 inv_num, '$ 924.05' price from dual union all
                    select 4737 inv_num, 'X' price from dual union all
                    select 4737 inv_num, 'X' price from dual union all
                    select 4782 inv_num, 'X' price from dual union all
                    select 4782 inv_num, '$1111' price from dual union all
                    select 4782 inv_num, '$ 89.40' price from dual)
    -- end of mimicking your data: USE SQL below (replacing my_tab with your tablename)
    select inv_num,
           price
    from   (select inv_num,
                   price,
                   rn,
                   max(rn) over (partition by inv_num) max_rn
            from   (select inv_num,
                           price,
                           row_number() over (partition by inv_num order by price desc) rn
                    from   my_tab))
    where  case when max_rn != rn and price = 'X' then 0
                else 1
           end = 1
    order by inv_num, price;
    
       INV_NUM PRICE   
    ---------- --------
          4720 $ 539.03
          4737 $ 924.05
          4782 $ 89.40 
          4782 $1111   
          9401 $ 52.32 
          9606 X       
          9613 X       
          9620 X       
          9644 X       
          9668 $34.56
    Edited by: Boneist on 28-May-2009 17:05

    Edited by: Boneist on 28-May-2009 17:08
    I think I've covered all bases now - multiple 'X' rows per inv_num, multiple price rows per inv_num
    Boneist
  • Jimmie_M
    Jimmie_M Member Posts: 150
    Wow!
    thank you so much for sharing your knowledge.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I reduced InlineView ;-)
    with my_tab as (select 9606 inv_num, 'X' price from dual union all
                    select 9606 inv_num, 'X' price from dual union all
                    select 9613 inv_num, 'X' price from dual union all
                    select 9620 inv_num, 'X' price from dual union all
                    select 9644 inv_num, 'X' price from dual union all
                    select 9668 inv_num, '$34.56' price from dual union all
                    select 9401 inv_num, 'X' price from dual union all
                    select 9401 inv_num, '$ 52.32' price from dual union all
                    select 4720 inv_num, 'X' price from dual union all
                    select 4720 inv_num, '$ 539.03' price from dual union all
                    select 4737 inv_num, '$ 924.05' price from dual union all
                    select 4737 inv_num, 'X' price from dual union all
                    select 4737 inv_num, 'X' price from dual union all
                    select 4782 inv_num, 'X' price from dual union all
                    select 4782 inv_num, '$1111' price from dual union all
                    select 4782 inv_num, '$ 89.40' price from dual)
    select inv_num,
           price
    from   (select inv_num,
                   price,
            row_number() over (partition by inv_num order by price desc) rn,
            count(*) over(partition by inv_num) as max_rn
               from   my_tab)
    where  case when max_rn != rn and price = 'X' then 0
                else 1
           end = 1
    order by inv_num;
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited May 2009
    Aha, thanks! Should have thought of that myself {noformat}*files it away for future reference*{noformat}
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    If you've never really used analytic functions, I suggest you research them. They're very handy for being able to refer to other rows in the query, without the need for self-joins, etc. They're not as complicated as they seem at first glance *{:-)
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I used nullif :D
    with my_tab as (select 9606 inv_num, 'X' price from dual union all
                    select 9606 inv_num, 'X' price from dual union all
                    select 9613 inv_num, 'X' price from dual union all
                    select 9620 inv_num, 'X' price from dual union all
                    select 9644 inv_num, 'X' price from dual union all
                    select 9668 inv_num, '$34.56' price from dual union all
                    select 9401 inv_num, 'X' price from dual union all
                    select 9401 inv_num, '$ 52.32' price from dual union all
                    select 4720 inv_num, 'X' price from dual union all
                    select 4720 inv_num, '$ 539.03' price from dual union all
                    select 4737 inv_num, '$ 924.05' price from dual union all
                    select 4737 inv_num, 'X' price from dual union all
                    select 4737 inv_num, 'X' price from dual union all
                    select 4782 inv_num, 'X' price from dual union all
                    select 4782 inv_num, '$1111' price from dual union all
                    select 4782 inv_num, '$ 89.40' price from dual)
    select inv_num,price
      from (select inv_num,price,
            Row_Number() over(partition by inv_num order by nullif(price,'X')) as rn
            from my_tab)
     where price != 'X' or rn = 1
    order by inv_num,price;
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Nice! {noformat} makes another note {noformat}
This discussion has been closed.