Need help with a query

1 Jul 2005 1:45 PM

Glen K

Say I have a table that contains pricing information for some products as
well as a count of the number of retailers selling at that price:

product_id
price
freq

Is there an easy way to get the lowest price for a product AND it's
corresponding frequency?

For example, if the table had the following data:

1,5.99,7
1,7.59,5
2,4.99,6
2,6.99,3

the query would return:

1,5.99,7
2,4.99,6

1 Jul 2005 1:55 PM

Jens Süßmeyer

Select * from Products P
INNER JOIN
(
Select MAX(price),ProductID From Products
Group by ProductId
) SUbQUery
ON SubQUery.ProductId = P.ProductID

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Glen K" <g***@blah.net> schrieb im Newsbeitrag
news:pan.2005.07.01.13.45.11.388417@blah.net...
> Say I have a table that contains pricing information for some products as
> well as a count of the number of retailers selling at that price:
>
> product_id
> price
> freq
>
> Is there an easy way to get the lowest price for a product AND it's
> corresponding frequency?
>
> For example, if the table had the following data:
>
> 1,5.99,7
> 1,7.59,5
> 2,4.99,6
> 2,6.99,3
>
> the query would return:
>
> 1,5.99,7
> 2,4.99,6
>

1 Jul 2005 3:40 PM

Jens Süßmeyer

SOrry sure should be MIN rather than MAX.

Show quote

"Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:eGHfJSkfFHA.460@TK2MSFTNGP09.phx.gbl...
> Select * from Products P
> INNER JOIN
> (
> Select MAX(price),ProductID From Products
> Group by ProductId
> ) SUbQUery
> ON SubQUery.ProductId = P.ProductID
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
> "Glen K" <g***@blah.net> schrieb im Newsbeitrag
> news:pan.2005.07.01.13.45.11.388417@blah.net...
>> Say I have a table that contains pricing information for some products as
>> well as a count of the number of retailers selling at that price:
>>
>> product_id
>> price
>> freq
>>
>> Is there an easy way to get the lowest price for a product AND it's
>> corresponding frequency?
>>
>> For example, if the table had the following data:
>>
>> 1,5.99,7
>> 1,7.59,5
>> 2,4.99,6
>> 2,6.99,3
>>
>> the query would return:
>>
>> 1,5.99,7
>> 2,4.99,6
>>
>
>

1 Jul 2005 3:13 PM

Jeremy Williams

Try something like the following:

SELECT product_id, price, freq
FROM Products INNER JOIN
     (SELECT product_id, MIN(price) as price
     FROM Products
     GROUP BY product_id) MinPrice
     ON Products.product_id = MinPrice.product_id
     AND Products.price = MinPrice.price

Of course, you should substitute your table's name for "Products"

Show quote

"Glen K" <g***@blah.net> wrote in message
news:pan.2005.07.01.13.45.11.388417@blah.net...
> Say I have a table that contains pricing information for some products as
> well as a count of the number of retailers selling at that price:
>
> product_id
> price
> freq
>
> Is there an easy way to get the lowest price for a product AND it's
> corresponding frequency?
>
> For example, if the table had the following data:
>
> 1,5.99,7
> 1,7.59,5
> 2,4.99,6
> 2,6.99,3
>
> the query would return:
>
> 1,5.99,7
> 2,4.99,6
>