Best SQL Algorithm?

1. Junior Member
Join Date
Dec 1969
Posts
4

## Best SQL Algorithm?

Hey all,<BR><BR>I&#039;m a network engineer who has discovered that dB&#039;s are wonderful tools. Hence I use them a lot but have had no formal instruction in dB design or SQL. Right now I&#039;m having a problem figuring out how to best structure a query to extract the data I want. Probably trivial for someone with more education or experience but it&#039;s killing me! Here&#039;s a simplified example of what I need to do:<BR><BR>This is my raw table called PriceList:<BR><BR>Store Item Price<BR>---------------------------------<BR>BigBuy Apples \$1.00<BR>BigBuy Oranges \$3.00<BR>BigBuy Bananas \$5.00<BR>SuperSave Apples \$2.00<BR>SuperSave Oranges \$1.00<BR>SuperSave Pears \$2.50<BR><BR>Basically I want a query to pull out the lowest price option for each fruit AND the store that is selling it at that price. IE this is the output I want:<BR><BR>Store Item Price<BR>---------------------------------<BR>BigBuy Apples \$1.00<BR>BigBuy Bananas \$5.00<BR>SuperSave Oranges \$1.00<BR>SuperSave Pears \$2.50<BR><BR>I know that I can do it by creating a view/query, let&#039;s call it MinPrices as follows:<BR><BR>SELECT PriceList.Item, Min(PriceList.Cost) AS MinOfCost<BR>FROM PriceList<BR>GROUP BY PriceList.Item;<BR><BR>and then using the following query to get the output I want:<BR><BR>SELECT PriceList.*<BR>FROM PriceList INNER JOIN MinPrices ON (PriceList.Cost = MinPrices.MinOfCost) AND (PriceList.Item = MinPrices.Item);<BR><BR>but I feel that there has to be a better, more efficient way to do this - hopefully in a single query. Can anyone tell me what it is? If it is not possible can you explain why it is not conceptually possible with SQL?<BR><BR>Thanks,<BR>&#124/ Jeff &#124/<BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

...you don&#039;t *have* to create a view/query ahead of time, though.<BR><BR>You *can* simply dump the code for that view into the SQL:<BR><BR>SELECT PriceList.*<BR>FROM PriceList,<BR> ( SELECT PriceList.Item, Min(PriceList.Cost) AS MinOfCost<BR> FROM PriceList<BR> GROUP BY PriceList.Item<BR> ) As MinPrices<BR>WHERE PriceList.Cost = MinPrices.MinOfCost<BR>AND PriceList.Item = MinPrices.Item<BR><BR>That *may* not work in Access. Probably will, but Access does get confused by some "inner tables". Almost surely will work in SQL Server.<BR><BR>But, really, it&#039;s no more efficient than what you are doing. Esp. in Access, it might even be a tad less efficient.<BR><BR>p.s.: No diff between the "implicit join" that I use there and the explicit INNER JOIN you used. They both do the same thing. Just a matter of personal preference.<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
4

## Thanks!

Hey Bill,<BR><BR>Thanks for the feedback - just what I was looking for - true enough that you caught me out using Access but I&#039;m more interested in learning proper/efficient SQL technique than what Access will and won&#039;t do. In fact, going to be porting all of our MySQL and Access into Oracle in the coming months - the key is that I didn&#039;t want to have to create a static view everytime I wanted to do this type of a query.<BR><BR>&#124/ Jeff &#124/

4. Junior Member
Join Date
Dec 1969
Posts
4

Just a note that the SQL you provided does work in Access. Really cool - it never even occurred to me that you could just jam a select statement into the FROM clause!

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Doesn't work in MySQL 3.xx...

...but I understand the MySQL 4.x now supports such inner SELECTs. Haven&#039;t played w/ MySQL in two+ years, though.<BR><BR>And no idea whether it works, as coded, in Oracle. Probably, but...<BR><BR><BR>

6. Junior Member
Join Date
Dec 1969
Posts
4

## Works in Oracle plus...

Hey Bill,<BR><BR>Yeah, it works in Oracle too and I&#039;ve also been told that the following syntax will do it in Oracle but haven&#039;t verified it yet:<BR><BR>SELECT store, item, cost 2 FROM (SELECT store, item, cost, 3 DENSE_RANK () OVER (PARTITION BY item ORDER BY cost) as MinofCost 4 FROM PriceList) 5 WHERE MinofCost = 1<BR><BR>&#124/ Jeff &#124/

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•