CODEWITHSUNDEEP
oracle-databaselimitsubquery
jgauffin
jgauffin

Reputation: 101140

Subselect in oracle

I'm struggling with a subselect in oracle. I want to include the latest price from another table.

Here is my current attempt:

SELECT tab1.*
    (select price from 
      old_prices 
    where part_no=tab1.article_no 
     order by valid_from desc) as old_price,
FROM articles tab1
order by article_no

The sub select returns several rows which I think is the problem. But I do not know how to limit the number of rows in Oracle.

Upvotes: 0

Views: 1472

Answers (6)

Harrison
Harrison

Reputation: 9090

with old_prices as(
select level * 15 price ,
       mod (level ,5) part_no , --this is just to create a grouping type partno
       (sysdate - level ) valid_from
  from dual
connect by level < 100) 
  ,
articles as(
     select level , 
            mod(level , 5 ) article_no ,
            (sysdate + level) someOtherDateField
    From dual
    connect by level < 5
     )
SELECT tab1.* ,
       old_price.*
  from articles tab1 
       left join
       (
         select price,
                part_no ,
                valid_from ,
                rank() over(partition by part_no order by valid_from desc) rk
          from old_prices
       ) old_price
       on tab1.article_no = old_price.part_no
          and old_price.rk = 1
order by article_no ;

Here's another way!

LEVEL                  ARTICLE_NO             SOMEOTHERDATEFIELD        PRICE                  PART_NO                VALID_FROM                RK                     
---------------------- ---------------------- ------------------------- ---------------------- ---------------------- ------------------------- ---------------------- 
1                      1                      25/05/11 07:30:54         15                     1                      23/05/11 07:30:54         1                      
2                      2                      26/05/11 07:30:54         30                     2                      22/05/11 07:30:54         1                      
3                      3                      27/05/11 07:30:54         45                     3                      21/05/11 07:30:54         1                      
4                      4                      28/05/11 07:30:54         60                     4                      20/05/11 07:30:54         1

Upvotes: 0

Rob van Wijk
Rob van Wijk

Reputation: 17705

SQL> create table articles (article_no,name)
  2  as
  3  select 1, 'PEN' from dual union all
  4  select 2, 'PAPER' from dual
  5  /

Table created.

SQL> create table old_prices (part_no,valid_from,price)
  2  as
  3  select 1, date '2008-01-01', 10 from dual union all
  4  select 1, date '2009-01-01', 11 from dual union all
  5  select 1, date '2010-01-01', 12 from dual union all
  6  select 1, date '2011-01-01', 13 from dual union all
  7  select 2, date '2010-01-01', 89.95 from dual union all
  8  select 2, date '2011-01-01', 94.95 from dual union all
  9  select 2, date '2012-01-01', 99.95 from dual
 10  /

Table created.

SQL> select a.article_no
  2       , max(a.name) keep (dense_rank last order by p.valid_from) name
  3       , max(p.price) keep (dense_rank last order by p.valid_from) price
  4    from articles a
  5       , old_prices p
  6   where a.article_no = p.part_no
  7   group by a.article_no
  8  /

ARTICLE_NO NAME       PRICE
---------- ----- ----------
         1 PEN           13
         2 PAPER      99.95

2 rows selected.

Regards,
Rob.

Upvotes: 2

schurik
schurik

Reputation: 7928

SELECT tab1.*
    (select 
         price 
     from (
            SELECT 
              part_no
            , price
            , row_number () over (partition by part_no order by valid_from desc ) rn
           FROM
             old_prices
           ) P
      where rn =1
      and tab1.article_no = P.part_no
     )                                              as old_price
FROM articles tab1
order by article_no

more efficient would be

SELECT 
    tab1.*
  , P.price
FROM 
    articles tab1
  , ( SELECT 
          part_no
        , price
        , row_number () over (partition by part_no order by valid_from desc ) rn
      FROM
          old_prices
    ) P 
WHERE 
  P.part_no(+) = tab1.article_no
  P.rn(+)      = 1
;

Upvotes: 0

arnep
arnep

Reputation: 6241

To limit rows use ROWNUM < 10. This is a pseudocolumn returning the row number of each line of your resultset.

EDIT:

You need to add another subselect query (hope this is the right place for your need)

SELECT tab1.*
  select (
    (select price from old_prices 
       where part_no=tab1.article_no order by valid_from desc
    ) as x
    where rownum = 1
  ) as old_price
FROM articles tab1
order by article_no

Upvotes: 0

symcbean
symcbean

Reputation: 48357

I want to include the lastest price

I presume you mean latest.

OK, well that's a bit of a problem to start with, there are several ways of doing this:

SELECT o.price
FROM old_prices o
WHERE o.part_no=&part_no
AND o.ondate=(SELECT MAX(o2.ondate)
     FROM old_prices o2
     WHERE o2.part_no=&part_no);

Seems the most obvious choice but its rather innefficient.

You could try....

SELECT ilv.price
FROM (SELECT o.price 
   FROM old_price o
   WHERE o.part_no=&part_no
   ORDER BY ondate DESC) ilv
WHERE rownum=1;

Or....

SELECT TO_NUMBER(
   SUBSTR(
      MAX(TO_CHAR(o.ondate, 'YYYYMMDDHH24MISS') || price)
      , 15)
   ) as latest_price
FROM old_price o
WHERE o.part_no=&part_no;

Upvotes: 0

Datajam
Datajam

Reputation: 4231

If it's the latest price you're after:

SELECT tab1.*, p.price old_price
FROM articles tab1
,    old_prices p
where p.part_no = tab1.article_no
and valid_from = (
    select MAX(valid_from)
    from   old_prices p2
    where  p2.part_no = p.part_no
)
order by article_no

Upvotes: 0

Related Questions