---------- Forwarded message ---------- From: sheeri kritzer <[EMAIL PROTECTED]> Date: Mar 27, 2006 1:18 PM Subject: Re: Insuring select returns the last record for a given day. To: Paul Halliday <[EMAIL PROTECTED]>
I have a similar table, so I tried out your query on the table I have: select created,date_format(created,'%Y-%m-%d') as day from Users where date_sub(curdate(),interval 14 day)<created group by day; (my dates are 'datetime' not unix timestamp) And in fact I got a random time of day, not the actual last one -- usually closer to the beginning of the day, actually. The following query worked for me: select date_format(max(created),'%Y-%m-%d'), date_format(created,'%Y-%m-%d') as day from Users where date_sub(curdate(),interval 7 day)<created group by day; You have to do the date format twice -- in my example, the first field I select is the maximum, the second is what you group on. You cannot group on the max field by itself. (a simple "select max(created) as day from Users group by day;" got me "ERROR 1056 (42000): Can't group on 'day'") hope it helps! -Sheeri On 3/27/06, Paul Halliday <[EMAIL PROTECTED]> wrote: > Hi, > > I have a table that looks somthing like this: > > ID timestamp campus IDS ePO inbound > outbound stat info > > 228941 1143430287 MA 0 0 424526713 > 284590944 0 NULL > 228940 1143430002 ST 2 0 290248558 > 119939485 0 NULL > 228939 1143430290 AN 1220 0 1697436588 > 2083621784 0 NULL > > I am trying to read the last record for each column for a given day > (when the stats are input they are already a sum, so the last entry > will be the cumulative total for each day). My query looks like this: > > select campus,date_format(from_unixtime(timestamp),'%Y-%m-%d') as > day,inbound,outbound,IDS,ePO from stats where > date_sub(curdate(),interval 7 day) <= from_unixtime(timestamp) and > campus='MA' group by day; > > campus day inbound outbound IDS ePO > MA 2006-03-21 6185842623 587934919 74 0 > MA 2006-03-22 8570249873 982444657 2 0 > MA 2006-03-23 8745243413 847904889 5 0 > MA 2006-03-24 4856668982 854718766 1 0 > MA 2006-03-25 798980766 250534732 0 0 > MA 2006-03-26 424526713 284590944 0 0 > MA 2006-03-27 144573737 59843102 79 0 > > So this query returns the last value for each day for the past 7 days > grouped by day. Tentative testing shows that the values are indeed > always the last entries but is this the right way to pull off this > query? I dont see anything specific in the query itself to insure it > extracts the last record for said day. > > Is this correct or should the query have further processing. > > Thanks. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
