MySQL query not getting the correct value

Question

I have a table numberoffirms like this:

--------------------------------------------
| id | region | month | year |    value    |
--------------------------------------------
|   1|       1|      1|     1|      1024001|
|   2|       1|      7|    25|        33542|
|   3|       1|      3|    26|     10000000|
|   4|       1|      5|    26|         1995|
|   5|       2|      4|    16|       123456|

My problem is that I need to get the highest value per region based on highest month and year. For example, from the table above, i should get the value 1995 because in region 1, the max year is 26 and in that year, the max month is 5.

Now when i run this SQL query which i made, it does not get the right value.

SELECT region, MAX(month) month, year, value FROM `numberoffirms` WHERE year IN (SELECT MAX(year) year FROM `numberoffirms` GROUP BY region) GROUP BY region

The value it gets is this

| region | month | year |   value  |
------------------------------------
|       1|      5|    26| 10000000 |

which is clearly wrong since the value i should get is 1995, though it got the region, month, and year correct.

What am i doing wrong with my SQL query code here?

Mostafa Vatanpour · Accepted Answer · 2016-05-30 05:01:45Z

1

You can simply use order by and limit 1 in a sub query:

Select * from numberoffirms t1 where id=
  (SELECT id FROM numberoffirms t2 
  where t1.region=t2.region
  order by year desc,month desc,value desc
  limit 1);

I created your table and values in my PC and tested the query and it works correctly.

edited May 30, 2016 at 5:01

answered May 30, 2016 at 3:20

Mostafa Vatanpour

1,44816 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Giorgi Nakeuri · Accepted Answer · 2016-05-29 12:02:29Z

0

In MySql it is a little bit complicated:

select n2.* from numberoffirms n2
join(
      select n1.region, n1.year, max(n1.month) as month numberoffirms n1
      join (select region, max(year) as year from numberoffirms group by region) s1
         on n1.region = s1.region and n1.year = s1.year
      group by n1.region, n1.year) s2 
  on n2.region = s2.region and n2.year = s2.year and n2.month = s2.month

answered May 29, 2016 at 12:02

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

MySQL query not getting the correct value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related