Hello, I have a table that looks like this: category price Store A 1 Walmart A 3 Kroger A 4 Publix B 3 Publix B 5 Target B 5 Walmart B 6 Kroger C 2 Kmart C 4 Walmart C 5 Target C 8 Kroger I want to select the lowest price from each category, for example A 1 Walmart, B 3 Publix, C 2 Kmart. How do I structure this query?
i haven't tested this out but... try this: select category, min(price), store from table group by category
$check = mysql_query("SELECT * FROM table"); while($rowcheck = mysql_fetch_array($check, MYSQL_NUM)) { /* put results in variable, let while loop keep looping, check for previous variables and compare them all. */ } PHP:
but isn't a while loop really inefficient in terms of processing required? This is a huge database and I will need it to process quickly. Also, as for the earlier suggestion: SELECT MIN(column) FROM table I can't just select one min column, I need the minimum price for each category, and also the corresponding store.
You will have to make the decision yourself. The exact scenario no one knows. But you will have to sacrifice some processing for some flexibility and have to manage with the query. If you get the query for exactly what you are looking for and there are one or two features which will require further queries then you will have to make a decision on the weightage that how much you want to sacrifice the processing or the flexibility and power.
I haven't tried, but I think SELECT MIN(price) AS price, Store AS Store FROM table WHERE Category="A" should do it. Of course, you would then have to change Category="A" to check for B, C, etc. Edit: I would give daboss' solution a try, too. If it works you would get all the categories in your database with just one single query, while mine requires multiple queries, which could be useful if you only need a few categories instead of all.
Selecting ALL rows is never a good idea unless you really need them. SELECT category, MIN(price) AS price, store FROM table_name GROUP BY category Code (markup):