CODEWITHSUNDEEP
mysql
goodbyeworld
goodbyeworld

Reputation: 41

MySQL query not getting the correct value

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?

Upvotes: 4

Views: 167

Answers (2)

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

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.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 0

Related Questions