Select minimum results

[heals1ic]

I have a table structured as such:

customerid, productid, price

I would like to extract the minimum price for each product and which supplier it is.

this is my code so far:

SELECT supplierid, productid, min(price) FROM `sup_prices` group by price order by productid

This gives me all the records.

Any idea how to modify this code to do what I need?

 

[JarlH]

First, when GROUP BY the columns in the SELECT list must either be specified in the GROUP BY clause or be the argument of a set function. This means that your SELECT statement is invalid since supplierid isn't specified in the GROUP BY clause! (Should return an error and *not* execute...)

Still this is not what you are asking for.

What you want is something like:

SELECT supplierid,productid,price
FROM sup_prices
WHERE (productid,price) IN
  (SELECT productid, min(price)
   FROM sup_prices
   GROUP BY productid)
ORDER BY productid;

 

[JarlH]

Or like this:

SELECT supplierid,productid,price
FROM sub_prices AS sp1
WHERE price = (SELECT MIN(price)
               FROM sub_prices AS sp2
               WHERE sp1.productid = sp2.productid
               GROUP BY productid)
ORDER BY productid;

Core SQL-2003.

 

[PHV]

Another way:
SELECT A.supplierid, A.productid, A.price
FROM sup_prices A INNER JOIN (
SELECT productid, MIN(price) minprice FROM sup_prices GROUP BY price
) B ON A.productid = B.productid AND A.price = B.minprice
ORDER BY 2

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

 

[r937]

JarlH, your 2nd query should remove the GROUP BY from the subquery -- it's ambiguous as it stands, and it's not necessary

r937.com | rudy.ca

 

[JarlH]

Yes, that GROUP BY may be removed! (Too much cut'n paste...)

 

[heals1ic]

Thanks for your replys guys

I have taken your suggestions and developed this expression

SELECT supplier,product,price_each as price, sup_prices.prodid as prodid 
FROM products, sup_prices, suppliers 
WHERE (sup_prices.prodid,price_each) 
IN (SELECT sup_prices.prodid, min(price_each) 
    FROM sup_prices GROUP BY sup_prices.prodid) 
and products.prodid = sup_prices.prodid 
and suppliers.supplierid = sup_prices.supplierid 
and catid = 2 
and product LIKE '%%%' 
order by 'product'

This unfortunately takes 30seconds to run this query.

Is there anyway to increase the performance of this query?

 

[Golom]

It would probably run better with JOINs

SELECT supplier,product,price_each as price, R.prodid 

FROM (products P INNER JOIN sup_prices R
      ON P.prodid = R.prodid)

      INNER JOIN suppliers S
      ON S.supplierid = R.supplierid 

WHERE (R.prodid,price_each) 
   IN (SELECT sup_prices.prodid, min(price_each) 
       FROM sup_prices GROUP BY sup_prices.prodid) 

  AND catid = 2  

order by 'product'

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[heals1ic]

I tried your query against the database and not really and difference in speed.

Is there another way of speeding this query up such as indexing some fields or to change the table structure from -

PRODUCTS table
| prodid | catid | product |
------

SUP_PRICES table
| prodid | catid | price_each |
------ -----

to

PRODUCTS table
| prodid | catid | product | sup_1_price | sup_2_price | sup_3_price | etc...
------

This would decrease the number of records needed to traverse to get the result for the above query.

Query speed is quite important as this query will be run often by many people concurrently.

Any ideas?

 

[heals1ic]

-- Sorry some mistakes in the last post -- (I cannot edit it after posting)

I tried your query against the database and not really and difference in speed.

Is there another way of speeding this query up such as indexing some fields or to change the table structure from -

PRODUCTS table
| prodid | catid | product |
------

SUP_PRICES table
| prodid | supplierid | price_each |
------ ----------

This is the normalised structured solution I believe

to

PRODUCTS table
| prodid | catid | product | sup_1_price | sup_2_price | sup_3_price | etc...
------

This would decrease the number of records needed to traverse to get the result for the above query but move away from a normalised database structure.

Query speed is quite important as this query will be run often by many people concurrently.

Any ideas?

 

[Golom]

It appears that you need indexes on ProdID and SupplierID in both tables where they occur.

You might also pull the subquery out and run it separately to see if it is the sub query or the main query that is slow. If the subquery is slow by itself then add an index on Price_Each in the sup_prices table.

I'm not a fan of denormalizing for performance until you are convinced that all normalized possibilities have been exhausted AND you're willing to pay the price for maintaining all that redundant data.

This is a fairly simple query as such things go and I would guess that your tables must be fairly large and/or unindexed if it's taking a long time to run.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[heals1ic]

I have 8 suppliers, 328 products totalling 2625 records in the sup_prices table.

Tried the sub query by it's self and no probs. But the query as a whole still takes around 35 sec.

I am not using any indexing at the moment as I am unsure where to apply the indexing.

Do you have any experience in applying indexing?

I read an article that suggested applying an index to the fields in your where clause. It also told me you can get clues by running the query with explain prefixed to it.

 

[Golom]

Each RDBMS usually has its own techniques for creating indexes but since this is the ANSI SQL forum

CREATE [ UNIQUE ] INDEX index
ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

is the general form of the DDL action statement.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[JarlH]

Actually the ANSI/ISO standard doesn't even mention indexes.

 

[r937]

JarlH, what a nice tactful reply

Golom, thanks for the gentle reminder about what forum we are in here

where did things go off topic? somewhere around the word "performance" i think

there is nothing wrong with talking about performance in general if we are talking about syntax variations such as NOT IN versus NOT EXISTS, or subquery versus join, ...

... but a good rule of thumb is that performance belongs in a dbms-specific forum

i'm surprised neither of you jumped all over this --

product LIKE '%%%'





r937.com | rudy.ca

 

[Golom]

... i'm surprised neither of you jumped all over this

I just dropped it in my response. Kinda assumed that "LIKE Everything" wasn't really a useful filter.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[heals1ic]

Sorry it was my fault that the product LIKE '%%%' was included in the supplied query. I could not edit it after I submitted the post.

I am also aware that this filter will have overheads on the query so I have removed it from my main query. Not much difference in performance.

Golom earlier you suggested

you need indexes on ProdID and SupplierID in both tables where they occur

both these fields are primary keys already and shouldn't be indexed I've been told.

If indexing is not helping, are there no syntactical improvements to the query that could increase performance?

Some more info to add to the mix.(it may not be relevant)
I am running a Fedora Core 5 linux box with 512 memory and P4 1.6G. When I submit the query the CPU % shoots up to 100 and stays until the result is returned. No significant memory increases or page file activity.

 

[r937]

both these fields are primary keys already and shouldn't be indexed I've been told.

that is correct, if you declare a column as a primary key, you should not create an(other) index on it


what about the foreign keys, though?


r937.com | rudy.ca

 

[PHV]

And what about this ?
SELECT S.supplier, P.product, A.price_each price, A.prodid
FROM products P
INNER JOIN sup_prices A ON P.prodid = A.prodid
INNER JOIN suppliers S ON A.supplierid = S.supplierid
INNER JOIN (SELECT X.prodid, MIN(X.price_each) minprice
FROM sup_prices X INNER JOIN products Y ON X.prodid = Y.prodid
WHERE Y.catid = 2 GROUP BY X.prodid
) B ON A.prodid = B.prodid AND A.price_each = B.minprice
WHERE P.catid = 2
ORDER BY 2

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

 

[heals1ic]

PVH Your a legend!!

Query took 0.0785 sec.

Cheers.

What a difference it makes to have added those extra inner joins.

Now I have to figure out why it works so well in place of the other queries.