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
>

query

from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still
showing?
CREATE TABLE Customers
(
Cust_ID int PRIMARY KEY,
Cust_Name char(20)
)
CREATE TABLE Orders
(
OrderID int PRIMARY KEY,
Cust_ID int REFERENCES Customers(Cust_ID)
)
GO
INSERT Customers VALUES (1, 'Cust 1')
INSERT Customers VALUES (2, 'Cust 2')
INSERT Customers VALUES (3, 'Cust 3')
INSERT Orders VALUES (10001, 1)
INSERT Orders VALUES (20001, 2)
GO
SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
'Orders.Cust_ID'=Orders.Cust_IDFROM Customers LEFT JOIN Orders ON
Customers.Cust_ID=Orders.Cust_ID and Customers.Cust_ID <> 2
Output:Customers.Cust_ID Cust_Name
Orders.Cust_ID -- -- --
1 Cust 1 12 Cust 2
NULL3 Cust 3 NULLThat is what a LEFT JOIN is for. To select all rows from left side and rows
from the right side that meet the join criteria. If you want to filter then
you should use the WHERE clause.
SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
'Orders.Cust_ID'=Orders.Cust_ID
FROM Customers LEFT JOIN Orders ON Customers.Cust_ID=Orders.Cust_ID
where Customers.Cust_ID <> 2
AMB
"js" wrote:

> from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still
> showing?
> CREATE TABLE Customers
> (
> Cust_ID int PRIMARY KEY,
> Cust_Name char(20)
> )
> CREATE TABLE Orders
> (
> OrderID int PRIMARY KEY,
> Cust_ID int REFERENCES Customers(Cust_ID)
> )
> GO
> INSERT Customers VALUES (1, 'Cust 1')
> INSERT Customers VALUES (2, 'Cust 2')
> INSERT Customers VALUES (3, 'Cust 3')
> INSERT Orders VALUES (10001, 1)
> INSERT Orders VALUES (20001, 2)
> GO
> SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
> 'Orders.Cust_ID'=Orders.Cust_IDFROM Customers LEFT JOIN Orders ON
> Customers.Cust_ID=Orders.Cust_ID and Customers.Cust_ID <> 2
> Output:Customers.Cust_ID Cust_Name
> Orders.Cust_ID -- -- --
--
> 1 Cust 1 12 Cust 2
> NULL3 Cust 3 NULL
>
>