Posted: 1/19/2010 9:41:10 AM EDT
|
Here it is I will show a stripped version of what I am after. I need the last entry per item per day. I get the last entry easy enough, but I need another piece to get me to the per day part.
Table "example" Key, Item, Value, EntryTime 0, 1, 45, 0300 1/19/2010 1, 2, 22, 1200 1/19/2010 2, 3, 43, 1100 1/19/2010 3, 1, 67, 0900 1/19/2010 4, 2, 87, 1500 1/19/2010 5, 3, 05, 0400 1/19/2010 6, 1, 24, 1800 1/18/2010 7, 2, 65, 0100 1/18/2010 8, 3, 23, 2200 1/18/2010 Result 6, 1, 24, 1800 1/18/2010 7, 2, 65, 0100 1/18/2010 8, 3, 23, 2200 1/18/2010 3, 1, 67, 0900 1/19/2010 4, 2, 87, 1500 1/19/2010 2, 3, 43, 1100 1/19/2010 ETA: It's Access, I hate Access, but it is what it is on this system. |
|
Could you do something like: select KEY, ITEM, VALUE, ENTRYTIME from EXAMPLE e1 where datepart(hour,ENTRYTIME) = select max(datepart(hour,ENTRYTIME)) from EXAMPLE e2 where convert(varchar,e1.ENTRYTIME,101) = convert(varchar,e2.ENTRYTIME,101) I dunno I don't have a database to try it on atm |
|
Quoted:
Could you do something like: select KEY, ITEM, VALUE, ENTRYTIME from EXAMPLE e1 where datepart(hour,ENTRYTIME) = select max(datepart(hour,ENTRYTIME)) from EXAMPLE e2 where convert(varchar,e1.ENTRYTIME,101) = convert(varchar,e2.ENTRYTIME,101) I dunno I don't have a database to try it on atm I am running it, but it is either crashing or verrrrrrrry inefficient. |
|
The below gets me the latest in the whole table efficently, but it won't do a by day without being nested.
SELECT example.item, example.EntryTime, Example.value FROM Example as HD1 WHERE (Select Top 1 HD2.key From Example as HD2 where HD1.item = HD2.item ORDER BY HD2.entrytime DESC, HD2.key DESC) = HD1.key Group By HD1.item, entryTime, value ORDER BY item; |
|
This will work assuming you have a time along with the date: SELECT * FROM example WHERE EntryTime IN ( SELECT MAX(EntryTime) FROM example GROUP BY (CAST(DATEPART(month, EntryTime) as varchar(2)) + '-' + CAST(DATEPART(day, EntryTime) as varchar(2)) + '-' + CAST(DATEPART(year, EntryTime) as varchar(4))) ) |
|
Quoted: This will work assuming you have a time along with the date: SELECT * FROM example WHERE EntryTime IN ( SELECT MAX(EntryTime) FROM example GROUPBY (CAST(DATEPART(month, EntryTime) as varchar(2)) + '-' +CAST(DATEPART(day, EntryTime) as varchar(2)) + '-' +CAST(DATEPART(year, EntryTime) as varchar(4))) ) ^This |
|
BTW, You should read this too.  http://www.w3schools.com/SQl/sql_select.asp |