List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:March 27 2006 6:18pm
Subject:Fwd: Insuring select returns the last record for a given day.
View as plain text  
---------- Forwarded message ----------
From: sheeri kritzer <awfief@stripped>
Date: Mar 27, 2006 1:18 PM
Subject: Re: Insuring select returns the last record for a given day.
To: Paul Halliday <paul.halliday@stripped>


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 <paul.halliday@stripped> 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/mysql?unsub=1
>
>
Thread
Fwd: Insuring select returns the last record for a given day.sheeri kritzer27 Mar