3

I have problem in selecting other columns for the max(datetime) records. In layman terms I need to get relevant columns where max(DialDateTime) records for all the dates in Mysql.

 mysql> select max(DialDateTime) as max from log_AP group by   date(DialDateTime) ;
 +---------------------+
 | max                 |
 +---------------------+
 | 2012-12-03 07:37:26 | 
 | 2012-12-04 07:37:04 | 
 | 2012-12-05 07:37:04 | 
 | 2012-12-06 07:37:04 | 
 | 2012-12-07 07:37:04 | 
 | 2012-12-08 07:37:04 | 
 | 2012-12-09 07:37:04 | 
 +---------------------+

7 rows in set (0.00 sec)

  • Based on the sample above, what is the desired result of your query? – Taryn Dec 13 '12 at 13:58
  • I want to get other columns for the relevant max(DialDateTime) field for all the dates. – Mannoj Dec 13 '12 at 14:02
6

You should be able to use a subquery to get the max date and then join that to you table to return the remaining columns:

select a1.*
from log_AP a1
inner join
(
  select max(DialDateTime) as max 
  from log_AP 
  group by date(DialDateTime)
) a2
  on a1.DialDateTime = a2.max
| improve this answer | |
  • perfect worked exactly the same way how I want.. Thanks!! – Mannoj Dec 13 '12 at 14:41
  • Caveat, though: this assumes the values found in DialDateTime are unique. If there were two or more rows with exactly the same value (same as that day's max()), you'd get multiple hits from log_AP for that date because you'd match multiple rows for that day with the join. – Michael - sqlbot Dec 13 '12 at 20:17
  • right so I have kept this sql in another outer query and group by outertable.DialDateTime - > In this case I will get only one row for the max(dates) for each day and its relevant records. I just need any one from that list, just a proof that no calls were made after 8.00 :) – Mannoj Dec 14 '12 at 9:02
2

Okay so I'm not mysql person so my syntax may not be right here but what I think you're looking for is this.

Select * from log_AP where DATE(DialDateTime) = (select max(DialDateTime) from log_AP) 
group by   DialDateTime

This way you are selecting all the columns where the dial datetime = that day.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.