SQL Select rows with max and min date

In case you ever have multiple rows with an identical date, this query will make sure only one row is returned for the min / max (only for Sql 2005+). ;WITH dates AS (SELECT dbo.Mydatabase(id) AS id, dbo.mydatabase.DATE AS [date], dbo.mydatabase.datain AS [in], dbo.mydatabase.dataout AS [out], Row_number() OVER (ORDER BY DATE ASC) AS row FROM dbo.mydatabase WHERE DATE >= Dateadd(DAY, Datediff(DAY, 0, Getdate()) - 7, 0) AND dbo.mydatabase.machineid = '7B788EE88E-6527-4CB4-AA4D-01B7F4048559'), dates2 AS (SELECT id, DATE, in, OUT, row, MIN(row) OVER (PARTITION BY (SELECT NULL)) AS lowest_row, MAX(row) OVER (PARTITION BY (SELECT NULL)) AS highest_row FROM dates) SELECT id, DATE, in, OUT FROM dates2 WHERE row = lowest_row OR row = highest_row

From reading the comments, and each machine doing its own INSERT, the insert wouldn't be inserting a value for the auto-increment column as that is handled by the engine. So, unless the machine is changing its date/time, there IS a direct correlation to the auto-increment ID to the date/time on a PER MACHINE basis. So, that said, and the sample I've created which gets the minimum and maximum ID per the MACHINE where the date/time is qualified WILL result in the definitive first and last ID for the range in question. Then you can get the specific ID records. So, if you have 3 machines, and they do an insert at exact same time, their respective IDs would be generated differently... |id |date |MachineId | -----+-------+-------+----------------- |1 |2011-10-25 09:03:17.000| A |2 |2011-10-25 09:03:17.000| B |3 |2011-10-25 09:03:17.000| C |4 |2011-10-26 12:45:43.000| B |5 |2011-10-26 12:45:43.000| A |6 |2011-10-26 12:45:43.000| C ... |124 |2011-10-29 13:29:04.000| C |125 |2011-10-29 13:29:04.000| A |126 |2011-10-29 13:29:04.000| B |127 |2011-10-31 21:18:35.000| C |128 |2011-10-31 21:18:35.000| B |129 |2011-10-31 21:18:35.000| A The first and last IDs per respective machine would become Machine First ID Last ID A 1 129 B 2 128 C 3 127 The inner pre-query is done once (PER THE SPECIFIC MACHINE), so you are getting the ID associated with the first/last instance per the machine date/time period. Then join that back to the table for the actual data with OR on the ID match. select D2.* FROM ( SELECT min( D1.ID ) MinDateID, max( D1.ID ) MaxDateID from dbo.myDatabase D1 where D1.MachineId = '7B788EE88E-6527-4CB4-AA4D-01B7F4048559' AND D1.Date >=dateadd(day,datediff(day,0,GetDate())- 7,0) ) PreQuery JOIN dbo.MyDatabase D2 on PreQuery.MinDateID = D2.ID OR PreQuery.MaxDateID = D2.ID