Problem with query

[Lokisame]

ello. I'm just starting with MySQL. I think I know quite a lot comparing to my practice. However I have a trouble with one query.

I have two tables in database. Let's say that one of those is table of products.

|ID|Product.|
|1.|Product1|
|2.|Product2|
|3.|Product3|

The second one is table with different prices of this products.
|ID|ProductID|Price|
|1 |1........|20...|
|2 |1........|30...|
|3 |1........|35...|
|4 |1........|50...|
|5 |2........|10...|
|6 |2........|60...|
|7 |2........|5....|
|8 |3........|2....|
|..|.........|.....|

The problem is I don't know how to match each product to its lowest price and the second lowest price.
For example, the result of query should look like this:

|Product |Lowest price|Second lowest price|
|Product1|20..........|30.................|
|Product2|5...........|10.................|
|........|............|...................|

I will appreciate any help from you.

 

[Andrzejek]

I would start with something like:
[tt]
Select Product,
(Select MIN(Price)
From Prices
Where ProductID = Products.ID) As LowestPrice
From Products
Order By ID[/tt]

And then work on Second Lowest Price.

Pozdrowionka


---- Andy

There is a great need for a sarcasm font.

 

[Lokisame]

Thank you for answer but this is the easy part. The problem starts with the second lowest price :S

 

[Andrzejek]

Can you just do as the Lowest Price, just eliminate the ID of the Lowest Price which should give you the Second Lowest from what's left.


---- Andy

There is a great need for a sarcasm font.

 

[fredericofonseca]

one way - there are others

with prices as
(select ProductID
      , Price
      , row_number() over (partition by ProductID
                               order by Price
                          ) as rownum
)
select product
     , price1.Price as LowestPrice
     , price2.Price as SecondLowestPrice
From Prices p1
outer apply (select top 1 Price
             from prices pr1
             where pr1.ProductID = p1.ID
             and rownum = 1
             ) price1
outer apply (select top 1 Price
             from prices pr1
             where pr1.ProductID = p1.ID
             and rownum = 2
             ) price2
Order By ID

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886