Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
Based on the sample above, what is the desired result of your query? –  bluefeet 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

2 Answers 2

up vote 5 down vote accepted

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
share|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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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