Query - perhaps GROUP BY

Hi,
Using SQL Server 2000
I have a table like this
Results
ResultID int primary key
ProductID char(10)
RetailerID int
RetailerPrice money
Some sample data might be
ResultID ProductID RetailerID RetailerPrice
1 1231231234 1 9.99
2 1231231234 2 19.99
3 1231231234 3 12.99
4 1231231235 1 11.99
5 1231231235 2 13.99
6 1231231235 3 3.99
I want to return the lowest price and it's resultid for a list of products.
I've got as far as
SELECT ProductID, MIN(RetailerPrice)
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID
which returns
ProductID RetailerPrice
1231231235 3.99
1231231234 9.99
What I want is
ProductID RetailerPrice ResultID
1231231235 3.99 6
1231231234 9.99 1
I've tried
SELECT ProductID, MIN(RetailerPrice), ResultID
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID, ResultID
but this returns every price for each product. I've tried various group by
clauses but have hit a brick wall. If anyone can point me in the right
direction I'd appreciate it.
Cheers,
JonLooks like a derived table:
SELECT
R.*
FROM
Results R
JOIN
(
SELECT ProductID, MIN(RetailerPrice) AS RetailerPrice
FROM Results
GROUP BY ProductID
) AS X ON X.ProductID = R.ProductID
AND X.RetailerPrice = R.RetailerPrice
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jon Spivey" <jons@.mvps.org> wrote in message
news:Ow$0XWdgGHA.4712@.TK2MSFTNGP05.phx.gbl...
Hi,
Using SQL Server 2000
I have a table like this
Results
ResultID int primary key
ProductID char(10)
RetailerID int
RetailerPrice money
Some sample data might be
ResultID ProductID RetailerID RetailerPrice
1 1231231234 1 9.99
2 1231231234 2 19.99
3 1231231234 3 12.99
4 1231231235 1 11.99
5 1231231235 2 13.99
6 1231231235 3 3.99
I want to return the lowest price and it's resultid for a list of products.
I've got as far as
SELECT ProductID, MIN(RetailerPrice)
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID
which returns
ProductID RetailerPrice
1231231235 3.99
1231231234 9.99
What I want is
ProductID RetailerPrice ResultID
1231231235 3.99 6
1231231234 9.99 1
I've tried
SELECT ProductID, MIN(RetailerPrice), ResultID
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID, ResultID
but this returns every price for each product. I've tried various group by
clauses but have hit a brick wall. If anyone can point me in the right
direction I'd appreciate it.
Cheers,
Jon|||Perfect. Thanks Tom.
Jon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23WI8MbdgGHA.3900@.TK2MSFTNGP05.phx.gbl...
> Looks like a derived table:
> SELECT
> R.*
> FROM
> Results R
> JOIN
> (
> SELECT ProductID, MIN(RetailerPrice) AS RetailerPrice
> FROM Results
> GROUP BY ProductID
> ) AS X ON X.ProductID = R.ProductID
> AND X.RetailerPrice = R.RetailerPrice
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Jon Spivey" <jons@.mvps.org> wrote in message
> news:Ow$0XWdgGHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Using SQL Server 2000
> I have a table like this
> Results
> ResultID int primary key
> ProductID char(10)
> RetailerID int
> RetailerPrice money
> Some sample data might be
> ResultID ProductID RetailerID RetailerPrice
> 1 1231231234 1 9.99
> 2 1231231234 2 19.99
> 3 1231231234 3 12.99
> 4 1231231235 1 11.99
> 5 1231231235 2 13.99
> 6 1231231235 3 3.99
> I want to return the lowest price and it's resultid for a list of
> products.
> I've got as far as
> SELECT ProductID, MIN(RetailerPrice)
> FROM Results
> WHERE ProductID IN('1231231234', '1231231235')
> GROUP BY ProductID
> which returns
> ProductID RetailerPrice
> 1231231235 3.99
> 1231231234 9.99
> What I want is
> ProductID RetailerPrice ResultID
> 1231231235 3.99 6
> 1231231234 9.99 1
> I've tried
> SELECT ProductID, MIN(RetailerPrice), ResultID
> FROM Results
> WHERE ProductID IN('1231231234', '1231231235')
> GROUP BY ProductID, ResultID
> but this returns every price for each product. I've tried various group by
> clauses but have hit a brick wall. If anyone can point me in the right
> direction I'd appreciate it.
> Cheers,
> Jon
>