Selecting Lowest Number from Multiple Options

Greetings,

I cannot quite figure out how to accomplish the following results.
My table is "Products" with the following fields:

ID Item Metal Size Price
1 Ring 18ctGold 4-7 $23.00
67 Ring 18ctGold 8-11 $30.00
70 Ring Silver 4-7 $10.00
75 Ring Silver 8-11 $15.00

I simply want to be able to show unique metal AND lowest price for each
metal; so, for above I would want to show rows with ID 1 and 70 only.
SELECT DISTINCT comes close but returns all distinct prices.

Would something like this call for sub-queries?
Any help from experts out there to figure this one is most appreciated.

Re: Selecting Lowest Number from Multiple Options

cs8404@hotmail.com wrote:
> Greetings,
>
> I cannot quite figure out how to accomplish the following results.
> My table is "Products" with the following fields:
>
> ID Item Metal Size Price
> 1 Ring 18ctGold 4-7 $23.00
> 67 Ring 18ctGold 8-11 $30.00
> 70 Ring Silver 4-7 $10.00
> 75 Ring Silver 8-11 $15.00
>
> I simply want to be able to show unique metal AND lowest price for
> each metal; so, for above I would want to show rows with ID 1 and 70
> only. SELECT DISTINCT comes close but returns all distinct prices.
>
> Would something like this call for sub-queries?
Yes. You would write a grouping query to retrieve the minimum price per
metal then join to that query to get the ID's. What do you want to do about
ties?

> Any help from experts out there to figure this one is most
> appreciated.

What database type and version are you using? The answer depends on the
answer to that.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Selecting Lowest Number from Multiple Options

"Bob Barrows [MVP]" wrote in message
news:%23mK7dM2yFHA.720@TK2MSFTNGP15.phx.gbl...
> cs8404@hotmail.com wrote:
>> Greetings,
>>
>> I cannot quite figure out how to accomplish the following results.
>> My table is "Products" with the following fields:
>>
>> ID Item Metal Size Price
>> 1 Ring 18ctGold 4-7 $23.00
>> 67 Ring 18ctGold 8-11 $30.00
>> 70 Ring Silver 4-7 $10.00
>> 75 Ring Silver 8-11 $15.00
>>
>> I simply want to be able to show unique metal AND lowest price for
>> each metal; so, for above I would want to show rows with ID 1 and 70
>> only. SELECT DISTINCT comes close but returns all distinct prices.
>>
>> Would something like this call for sub-queries?
> Yes. You would write a grouping query to retrieve the minimum price per
> metal then join to that query to get the ID's. What do you want to do
> about
> ties?
>
>> Any help from experts out there to figure this one is most
>> appreciated.
>
> What database type and version are you using? The answer depends on the
> answer to that.

Actually, I don't think the OP needs the ID in the results, just the metal
and lowest price. Assuming that's the case, the SQL is pretty generic:

SELECT metal, Min(price) AS LowestPrice
FROM MyTable GROUP BY metal

That will work in SQL Server, Jet, VFP, and if it didn't work in MySQL and
Oracle, I'd be surprised and disappointed. :-)

If I'm wrong, and the ID is required in the result set, the possibility of
multiple rows for a given metal, with the same [lowest] price do indeed pose
a sticky problem; all the ways to return it in a single rowset that I can
think of, are ugly. So if I needed an ID I'd probably look it up after the
fact, when the user expressed an interest in it... assuming that was
practical, of course.


-Mark




> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>