|
 |
asp_ado_rds thread: Buld a SQL query
Message #1 by "Tom" <AccessSyst@a...> on Fri, 22 Nov 2002 20:44:06
|
|
Hello All,
I have a SQL Server table tRates which looks like this:
------|--------|-----------
Rate | Point | Product
---------------------------
5.000 | 3.120 | 930
5.000 | 3.550 | 708
5.000 | 4.000 | 718
5.125 | 3.650 | 708
5.125 | 4.125 | 930
5.125 | 4.250 | 718
I need to select product with the lowest Point for individual Rate.
So result set will look like this :
------|--------|-----------
Rate | Point | Product
---------------------------
5.000 | 3.120 | 930
5.125 | 3.650 | 708
Thanks in advance.
-Tom
Message #2 by "Stephen Andrews" <my_ska@h...> on Sun, 24 Nov 2002 19:44:40 +1100
|
|
You will need 2 queries, one to group the rate and find the minimum point
and the second to join the product number to the result of the first.
Query 1: qryMinOfPointsGrouped
- SELECT Rate, 'MinOfPoint' = Min(Point) FROM tRates GROUP BY Rate;
Query 2: qryDisplay
- SELECT qryMinOfPointsGrouped.Rate, qryMinOfPointsGrouped.MinOfPoint AS
Point, Product FROM qryMinOfPointsGrouped INNER JOIN tRates ON
(qryMinOfPointsGrouped.Rate = tRates.Rate) AND
(qryMinOfPointsGrouped.MinOfPoint = tRates.Point);
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
Message #3 by "Tom" <AccessSyst@a...> on Mon, 25 Nov 2002 14:28:09
|
|
Thanks
>
You will need 2 queries, one to group the rate and find the minimum point
and the second to join the product number to the result of the first.
Query 1: qryMinOfPointsGrouped
- SELECT Rate, 'MinOfPoint' = Min(Point) FROM tRates GROUP BY Rate;
Query 2: qryDisplay
- SELECT qryMinOfPointsGrouped.Rate, qryMinOfPointsGrouped.MinOfPoint AS
Point, Product FROM qryMinOfPointsGrouped INNER JOIN tRates ON
(qryMinOfPointsGrouped.Rate = tRates.Rate) AND
(qryMinOfPointsGrouped.MinOfPoint = tRates.Point);
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
|
|
|
View Cart
