>>I have a delivery table with the following columns
>>
>>location c(10)
>>driver c(2)
>>pickupdate datetime
>>pickuptime c(8) 99:99:99
>>
>>I want to sequence pickups for a given driver, location and day of the week by pickup time.
>>One wrinkle is that there are lots of pickups for any day and there can be some odd times. I want to use the most common time for a day.
>>That is, I want to wind up a table with one row for each driver, location and day of the week sequenced as above
>>Another wrinkle is that I might want to do this in MySql as well as in SQL Server.
>>
>>Any ideas most welcome.
>
>In SQL Server the first idea that comes to mind:
>

>;with cte as (select *, count(*) over (partition by driver, location, datepart(weekday, pickupdate), pickuptime) as cntTimesPerDay from DeliveryTable),
>cte2 as (select *, row_number() over (partition by driver, location, datepart(weekday, pickupdate) order by cntTimesPerDay DESC) as Rn from cte)
>
>select * from cte2 where Rn = 1 -- rows with most common times per day per driver/location

Naomi...
I've been trying to put this into a view, and can't find any way to do it.
Can it be done?