🚀 go-pugleaf

Roy wrote:
> Hi,
> 
> I have an Interbase table with the following fields
> 
> START_DATETIME
> TRAFFIC
> ROUTE_NAME
> 
> I want to be able to retrieve the MAX(TRAFFIC) for a particular ROUTE_NAME 
> on a particular day (0ne record for the day) ;
> 
> but the Select statement
> 
> 
> SELECT START_DATE,ROUTE_NAME, MAX(TRAFFIC) from SYSTRAFFIC where 
> START_DATETIME  = '2007/1/1'  GROUP BY
> START_DATETIME,ROUTE_NAME
> 
> 
> returns all values for the traffic for all the hours of the day (24 Records)
> 
> How can I achieve that.
> 
> 
> Thanks very much for your help.
> 
> Rgds
> Roy 
> 
> 

You will get the max of the combination of START_DATE,ROUTE_NAME with this SQL. 
  To get the route and max traffic for that route use

SELECT ROUTE_NAME, MAX(TRAFFIC)
   from SYSTRAFFIC
  where START_DATETIME  = '2007/1/1'
  GROUP BY ROUTE_NAME

Then go back and get the start_date based on route_name, traffic and 
start_datetime.  This could probably be turned into a selectable stored 
procedure to get all three in a single DB call.


-- 
Jeff Overcash (TeamB)
       (Please do not email me directly unless  asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted  corridors of Whitehall.
              (Fish)