Question about MAX function

stevieooo · #1 (**permalink**) 03-05-07, 17:36

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.

r937 · #2 (**permalink**) 03-05-07, 18:47

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

stevieooo · #3 (**permalink**) 03-06-07, 08:42

Thank you ever so much. I was trying to make a mountain out of a molehill!!!

snorp · #4 (**permalink**) 03-09-07, 11:51

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).

stevieooo · #5 (**permalink**) 03-09-07, 11:55

Thank you for the tip. I will use the only_full_group_by mode.

Internet Services

Web Development

Digital Marketing

Consumer Tech