Need lowest price in each region in a mysql query

Question

Hi guys,

I am trying to write up a query for wordpress which will give me all the post_id's with the lowest fromprice field for each region. Now the trick is these are custom fields in wordpress, and due to such, the information is stored row based, so there is no region and fromprice columns.

So the data I have is (but of course containing a lot more rows):

Post_ID | Meta_Key  | Meta_Value
1       | Region    | Location1
1       | FromPrice | 150
2       | Region    | Location1
2       | FromPrice | 160
3       | Region    | Location2
3       | FromPrice | 145

The query I am endeavoring to build should return the post_id of the "lowest priced" matching post grouped by each region with results like:

Post_ID | Region | From Price
1       | Location1 | 150
3       | Location2 | 145

This will allow me to easily iterate the post_id's and print the required information, in fact, I would be just happy with returning post_id's if the rest is harder, I can then fetch the information independently if need be.

Thanks a lot, tearing my hair out over this one; don't often have to think about shifting results on their side from row based to column based that often, but this time I need it!

So you get an idea of the table structure I have, you can use the below as a guide. I thought I had this, but it turned out yes, this query prints out each distinct region WITH the lowest from price found attached to that post in the region, but the post_id is completely incorrect. I don't know why, it seems to be just getting the first result of the post_id and using that.

SELECT  pm.post_id, 
        pm2.meta_value as region, 
        MIN(pm.meta_value) as price 
FROM `wp_postmeta` pm 
inner join `wp_postmeta` pm2 
  on pm2.post_id = pm.post_id 
     AND pm2.meta_key = 'region'
     AND pm.meta_key = 'fromprice'
group by region

Answer 1

It's hard to figure out without being able to execute the query, but would it help to just change your group by to:

group by pm.post_id, region

Answer 2

I suggest changing MIN(pm.meta_value) in your query to be MIN(CAST(pm.meta_value AS DECIMAL)). Meta_value is a character field, so your existing query will be returning the minimum string value, not the minimum numeric value; for example, "100" will be deemed to be lower than "21".

EDIT - amended CAST syntax.