SQL Server Performance Forum – Threads Archive

distinct

I’m trying to do a selective query. This is what I currently have. select top 1 * from StatisticData where tagid = ‘ARM_MID_SCANNER_RATE’ and LEFT( CONVERT(varchar, DateTimeStamp, 120), 10) = ‘2004-08-12’ order by datetimestamp desc This works except that we#%92d like it to execute for a range of dates. Currently it returns the latest value from 2004-08-12. How can we get it to return more than just one? This table has 12 entries for each date (logs every hour) and we want the latest one only. I thought about using select distinct datetimestamp from StatisticData where tagid = ‘ARM_MID_SCANNER_RATE’ but that only returns the datetimestamp, and I want it to return all the rows. Thanks, Ben

---

select * from StatisticData
inner join
(
select distinct datetimestamp from StatisticData where tagid = ‘ARM_MID_SCANNER_RATE’
) x
on StatisticData.datetimestamp = x.datetimestamp Does that help? Dave Hilditch

---

I’m still getting all the rows with this method. I just want to get the latest entry for a selected date.

---

Ah right, I get what you’re asking now. select * from StatisticData
where tagid = ‘ARM_MID_SCANNER_RATE’ and DateTimeStamp in
(
select max(DateTimeStamp) from StatisticData — select max timestamp on a per day basis for this tagid
where tagid = ‘ARM_MID_SCANNER_RATE’
group by LEFT( CONVERT(varchar, DateTimeStamp, 120), 10) — group by day
) Haven’t tested, but this should get you close to what you’re looking for. Sorry, didn’t quite understand you first time I read it. You could also do an inner join to the subquery if you prefer to do it that way – might be an idea to test on your dataset and see which is faster. Dave Hilditch.

---

Thanks, I’ll try this out.

---

]]>