How to Get Row results based on Min

[dyluck]

Hi there... I can't seem to find a php query that works for the min function.

 

I am trying get the entire row's contents/results from a table based on the minimum value of one of the columns.... everything I have tried returns errors.

 

Here is some of the things I have tried:

"SELECT * FROM AUTOPODUCT WHERE PNAME = '%s' AND MIN('SALEPRICE')"

"SELECT MIN('SALEPRICE') FROM AUTOPODUCT WHERE PNAME = '%s' GROUP BY PNAME"

 

hope you can help!

[printf]

Don't use single or double quotes in your aggregate (GROUP BY) functions

 

MIN(SALEPRICE)  // correct, mysql interprets as column name
MIN('SALEPRICE')  // not correct, mysql interprets as a literal value SALEPRICE

[DavidAM]

It looks like you want the entire row for a particular product that has the lowest price.

 

One way to do this is to select the data sorted by price and only return one row:

SELECT * FROM AUTOPODUCT WHERE PNAME = '%s'  ORDER BY SALEPRICE LIMIT 1"

 

Another way to do it is using a subquery:

SELECT * FROM AUTOPODUCT WHERE PNAME = '%s' AND SALEPRICE =
(SELECT MIN(SALEPRICE) FROM AUTOPODUCT WHERE PNAME = '%s')"

 

If you have multiple rows with the same SALEPRICE (which is the lowest for the product) the first method will only return one of them and you will have no way to know that there is another.  The second method will return multiple rows and you can test the row count to see if there is more than one with that price.

[dyluck]

Thanks guys

Turns out with your help and the fact I spelled AUTOPRODUCT wrong i got it

 

"SELECT * FROM AUTOPODUCT WHERE PNAME = '%s'  ORDER BY SALEPRICE LIMIT 1"

Ended up being the one that worked.  The other one passed an error that said "too few arguments" and gave the SQL error.

Wonder why that is?

 

[DavidAM]

Probably because AUTOPRODUCT is spelled wrong there as well.  Also, since there is another place holder ('%s'), you have to pass the product name twice to fill them both in.