Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    13

    Unanswered: Question about MAX function

    Hi all. I have a VB6 application that accesses data from a MySql table and returns it. One of my columns is time_date that puts a timestamp when data is uploaded. If I have more than one recordset that will be returned, I only want the one with the greatest timestamp. Here is my query:
    Code:
    select reference,item1,item2,item3,item4,user_name,max(time_date) 
    from item_tracker where reference like ('" & txtReference.Text & "')  
    group by time_date
    The recordset that gets returned is the first one in the table and not the one with the latest timestamp. Can anyone help me so I can return the newest record(max(timestamp))? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select reference,item1,item2,item3,item4,user_name,time_d ate
    from item_tracker
    where reference like ('" & txtReference.Text & "')
    and time_date = ( select max(time_date) from item_tracker )

    this is not a GROUP BY problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    13
    Thank you ever so much. I was trying to make a mountain out of a molehill!!!

  4. #4
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71

    About MySQL and GROUP

    A recommendation related to your problem is to set "sql_mode" to, among other things, ONLY_FULL_GROUP_BY. This settings requires all columns on which aggregation functions are not applied to be specified in the GROUP BY clause. Your query would have terminated with an error since you did not mention all columns in your GROUP BY. Have a look in the documentation and read about the "modes" to see how you can make MySQL stricter (which prevents you from running some queries that would otherwise give you unexpected results).

  5. #5
    Join Date
    Feb 2007
    Posts
    13
    Thank you for the tip. I will use the only_full_group_by mode.

Posting Permissions

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