Results 1 to 8 of 8

Thread: Help with MAX()

  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Unanswered: Help with MAX()

    Hi,
    Need a query help.

    Select Serial_No, MAX(Log_Time), * from logs
    group by Serial_No

    Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

    How do I do it?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by rexselin
    Hi,
    Need a query help.

    Select Serial_No, MAX(Log_Time), * from logs
    group by Serial_No

    Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

    How do I do it?
    select * from Logs,
    (Select Serial_No, MAX(Log_Time) from logs
    group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

    I think that should be it ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    Sorry. That doesn't seem to give me the result I want.

    These are my data.

    insert into logs (serial_no, logid, log_time, event_reason)
    values ('SNI', '82738278372873','2005-10-10','Approved');

    insert into logs (serial_no, logid, log_time, event_reason)
    values ('SNI', '82738278372874','2005-10-11','Approved');

    insert into logs (serial_no, logid, log_time, event_reason)
    values ('SNI', '82738278372872','2005-10-12','Approved');

    insert into logs (serial_no, logid, log_time, event_reason)
    values ('SN2', '82738278372875','2005-10-13','Approved');

    insert into logs (serial_no, logid, log_time, event_reason)
    values ('SN2', '82738278372876','2005-10-14','Approved');


    I just want the latest records for each Serial_No. In this case, I would want
    the third and the fifth record.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Enigma
    select * from Logs,
    (Select Serial_No, MAX(Log_Time) from logs
    group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No
    Code:
    SELECT * 
    FROM Logs INNER JOIN 
    (SELECT Serial_No, MAX(Log_Time) AS MaxOfLogs 
    FROM logs 
    GROUP BY Serial_No) LogMaxTime ON 
    Logs.Serial_No = LogMaxTime.Serial_No 
    AND Logs.Log_Time = LogMaxTime.MaxOfLogs
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    It WORKS!!!
    Thanks a lot!!

  6. #6
    Join Date
    Mar 2006
    Posts
    2
    Quote Originally Posted by rexselin
    Hi,
    Need a query help.

    Select Serial_No, MAX(Log_Time), * from logs
    group by Serial_No

    Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

    How do I do it?
    Select Serial_No, MAX(Log_Time), * from logs
    group by Serial_No having Log_Time = MAX(Log_Time)

    or

    Select Serial_No, Log_Time , * from logs
    group by Serial_No having Log_Time = MAX(Log_Time)



    I think second is the best to use. Because it is Optimised.


    Regards,
    Subramanyam.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the second is not the best to use, because it is invalid syntax

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but other than that minor drawback, it is way ultra-cool.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •