CODEWITHSUNDEEP
sqloracle-databasegroup-by
IFlyHigh
IFlyHigh

Reputation: 546

Select min. three values of a record in another table with a JOIN

I have two tables:

Table 1: Article 

Article_ID         Price Valid from         Price ID        
1                  12-Aug-2014              1
1                  30-Dec-2014              2
1                  10-Oct-2014              3
1                  15-Oct-2014              5
2                  13-Aug-2014              1
---(n no. of records for article 2)
3                  01-July-2014             4   
3                  02-July-2014             5   
---(n no. of records for article 3)

Here the unique key is Article_Id and Valid from date.

Meaning each article have different prices which are valid from certain dates. An article can not have different prices on the same valid from date.

Table 2: Price

Price ID            Price
1                   10
2                   200
3                   30
4                   400
5                   50

Required result:

I want for each article Minimum three Prices. For this I need a join, but I am not able to figure out how to restrict the no. of rows for each article Price to 3, and how to implement the min. criteria.

Final result should be:

Article_ID                     Min Price1      Min Price2    Min Price3
1                              10              30            50
2                              10    
3                              50              400

An article will have max. of three prices (in the final result), minimun could be 0. And three Prices it has are the smallest prices of the article from the table 2.

I get the idea that it has to do something with min. (Group by) and row no. (with a join on Table 2), but can anyone help me with it's implementation?

Regards SJ

Upvotes: 1

Views: 549

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

If the requirement remains for a pivot of 3 prices in a series, then using ROW_NUMBER() will enable giving these numbers 1, 2, 3 and from there is is a simple use of case expressions with a group by:

for 3 lowest prices:

SELECT
      a.article_id
    , MAX(CASE
            WHEN a.price_seq = 1 THEN p.price END) AS price_1
    , MAX(CASE
            WHEN a.price_seq = 2 THEN p.price END) AS price_2
    , MAX(CASE
            WHEN a.price_seq = 3 THEN p.price END) AS price_3
FROM (
            SELECT
                  article_id
                , price_valid_from
                , price_id
                , ROW_NUMBER() OVER (PARTITION BY article_id
                                     ORDER BY p.price ASC) AS price_seq
            FROM article
                  LEFT OUTER JOIN price p
                              ON (a.price_id = p.price_id)
      ) a
GROUP BY
      a.article_id
ORDER BY
      a.article_id

for 3 most recent prices

SELECT
      a.article_id
    , MAX(CASE
            WHEN a.price_seq = 1 THEN p.price END) AS price_1
    , MAX(CASE
            WHEN a.price_seq = 2 THEN p.price END) AS price_2
    , MAX(CASE
            WHEN a.price_seq = 3 THEN p.price END) AS price_3
FROM (
            SELECT
                  article_id
                , price_valid_from
                , price_id
                , ROW_NUMBER() OVER (PARTITION BY article_id
                                     ORDER BY price_valid_from DESC) AS price_seq
            FROM article
      ) a
      LEFT OUTER JOIN price p
                  ON (a.price_id = p.price_id)
GROUP BY
      (a.article_id)
ORDER BY
      a.article_id

Demo SQLfiddle

Upvotes: 2

stackMonk
stackMonk

Reputation: 1033

This will fetch the min price of the articles in the article table limit will help you with getting the desired minimum value :

SELECT
        aa.article_id
      , (
        SELECT min(p.price)
        FROM article a
        LEFT JOIN price p ON (p.price_id = a.price_id)
        WHERE a.article_id = aa.article_id order by p.price limit 1,1
        ) AS minPrice1
    , (
        SELECT min(p.price)
        FROM article a
        LEFT JOIN price p ON (p.price_id = a.price_id)
        WHERE a.article_id = aa.article_id order by p.price limit 2,1
        ) AS minPrice2
    , (
        SELECT min(p.price)
        FROM article a
        LEFT JOIN price p ON (p.price_id = a.price_id)
        WHERE a.article_id = aa.article_id order by p.price limit 3,1
        ) AS minPrice3
FROM article aa;

Upvotes: 0

Related Questions