November 22, 2004 at 12:36 pm
I currently have tables that are comprised of stock tick prices minute by minute. I now need to query this data for only one record (first one) of each day for the last 30 days. I can get the last 30 days but can't seem to wrap my head around getting only one record for each day, efficiently.
Can someone point me in the right direction?
Here is what I am currently working with:
Select * from crudefilo where DT > DatePart(Day, DateAdd(day,-30,GetDate())) order by dt.
Many Thanks
November 22, 2004 at 3:20 pm
Kyle:
Try
select datepart(d,t1.dt), min(t1.dt) from crudefilo as t1
inner join crudefilo as t2 on t1.dt=t2.dt
Where t1.dt > DatePart(Day, DateAdd(day,-30,GetDate()))
group by datepart(d,t1.dt)
You might choose a different value to return as the base of the aggregate than I did in the example here, but you get the idea of using it as the base of the select and as part of the group by
warey
November 23, 2004 at 5:57 pm
Try the following:
select *
from crudefilo c1
where c1.dt >= DateAdd(day, -30, GetDate())
and c1.dt = (select min(c2.dt)
from crudefilo cw
where datepart(d,c2.dt) = datepart(d,c1.dt)
and datepart(m,c2.dt) = datepart(m,c1.dt)
order by c1.dt
Thomas Farren
November 24, 2004 at 9:15 am
Warey, If I needed to return more columns, do I have to use the same function (min()) on those as well?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply