Possible via SQL? 2

[bensgroi]

I'm not sure if it's possible to write a single SQL query to retrieve this information, but I figured this is the best place to ask.

Here's the table I'm working with:
DrugPrices (PharmID int, DrugID int, Price float, GenericPrice float)

What I want to do is select 4 distinct values from the table: the max and min of the Price column where DrugID = 11, and the max and min of the Price column where DrugID = 17.

I've pored over this and can't seem to figure it out... maybe this is where I need to switch to SQL Server and stored procedures! TIA

 

[hneal98]

You can link different instances of a single table as if it were multiple tables. Then you can pull the min and max from each if them.

You can look in help under self linking tables or something like that.

 

[bensgroi]

Okay I think I got it now:

SELECT Max(P1.Price) AS LHigh, Max(P2.Price) AS OHigh, Min(P1.Price) AS LLow, Min(P2.Price) AS OLow
FROM DrugPrices P1, DrugPrices P2
WHERE P1.DrugID = 11 AND P2.DrugID = 17

I didn't know you could draw from a table twice via different aliases. That makes it much easier. Thanks for the help!

 

[hneal98]

No problem.

 

[PHV]

And what about this ?
SELECT DrugID, Min(Price) As PriceMin, Max(Price) As PriceMax
FROM DrugPrices
WHERE DrugID In(11, 17)
GROUP BY DrugID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bensgroi]

Cool, another good solution. But I think I'm going to stick with the previous method, because it gives me the results in a single row. Thanks!

 

[PHV]

Yet another way, for single row result:
SELECT Min(Price) As Min11, Max(Price) As Max11
,(SELECT Min(Price) FROM DrugPrices WHERE DrugID=17) AS Min17
,(SELECT Max(Price) FROM DrugPrices WHERE DrugID=17) AS Max17
FROM DrugPrices
WHERE DrugID=11;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bensgroi]

Wow, this has to be the most useful forum I've ever come across.... Thanks again!

 

[hneal98]

PHV always has great suggestions. Showoff! Just kidding.