By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

query for max date value for each item in a WHERE IN clause

P: 20
Hello,

I am trying to query the data set below for the data_value for the data_id of the recent data_date.

So for data_id 18554 the recent data_date will be 2/8/2016 18:40 and its data_value will be 29504.76

And for data_id 18555 the recent data_date will be 2/8/2016 18:40 and its data_value will be -90909090909

Expand|Select|Wrap|Line Numbers
  1. data_id    data_name    data_date    data_value
  2. 18554    traffic_in    2/8/2016 18:30    30737.11333
  3. 18554    traffic_in    2/8/2016 18:10    31780.68
  4. 18554    traffic_in    2/8/2016 18:35    30338.82
  5. 18554    traffic_in    2/8/2016 18:15    31393.36667
  6. 18554    traffic_in    2/8/2016 18:40    29504.76
  7. 18554    traffic_in    2/8/2016 17:50    32225.28
  8. 18554    traffic_in    2/8/2016 18:00    32225.32
  9. 18554    traffic_in    2/8/2016 17:55    32240.71333
  10. 18554    traffic_in    2/8/2016 17:45    32602.98
  11. 18554    traffic_in    2/8/2016 18:20    30874.39333
  12. 18554    traffic_in    2/8/2016 18:25    30511.56
  13. 18554    traffic_in    2/8/2016 18:05    32090.84667
  14.  
  15. 18555    traffic_in    2/8/2016 18:15    -90909090909
  16. 18555    traffic_in    2/8/2016 18:35    -90909090909
  17. 18555    traffic_in    2/8/2016 17:45    -90909090909
  18. 18555    traffic_in    2/8/2016 18:00    -90909090909
  19. 18555    traffic_in    2/8/2016 17:50    -90909090909
  20. 18555    traffic_in    2/8/2016 17:55    -90909090909
  21. 18555    traffic_in    2/8/2016 18:10    -90909090909
  22. 18555    traffic_in    2/8/2016 18:20    -90909090909
  23. 18555    traffic_in    2/8/2016 18:05    -90909090909
  24. 18555    traffic_in    2/8/2016 18:25    -90909090909
  25. 18555    traffic_in    2/8/2016 18:40    -90909090909
  26. 18555    traffic_in    2/8/2016 18:30    0.213333333
  27.  
  28.  
The results i want is

Expand|Select|Wrap|Line Numbers
  1. data_id    data_name    data_date    data_value
  2. 18554    traffic_in    2/8/2016 18:40    29504.76
  3. 18555    traffic_in    2/8/2016 18:40    -90909090909
  4.  
But when i use the following MySQL statement
Expand|Select|Wrap|Line Numbers
  1. SELECT data_id,data_name,max(data_date),data_value FROM `data_source_` where `data_id` IN (18554,18555) AND data_name = 'traffic_in'
  2.  
this just gives me

Expand|Select|Wrap|Line Numbers
  1. data_id    data_name    data_date    data_value
  2. 18554    traffic_in    2/8/2016 18:40    29504.76
  3.  
Help will be very much appreciated.

Thanks
Feb 8 '16 #1
Share this Question
Share on Google+
1 Reply


hsriat
Expert 100+
P: 1,654
Something's missing...
Expand|Select|Wrap|Line Numbers
  1. group by
Feb 27 '16 #2

Post your reply

Sign in to post your reply or Sign up for a free account.