Query that will give the first row entered for each day

I've got a table with an auto开发者_C百科-increment number field As "No", a date/time stamp As "Entry_Date", and X, Y, Z fields. Every few minutes, My program inserts a new row with the current date/time.

I need a query that will give the first row entered for each day for the past 365 days (or whatever value I want to use). Please help...

I'd use ROW_NUMBER and partition by date. Nice and simple: (Note this is 2008, for use of DATE)

CREATE TABLE #TestDatePartition
(
    ID int IDENTITY(1,1),
    EntryTime datetime
)

INSERT INTO #TestDatePartition(EntryTime)
SELECT GETDATE() UNION ALL
SELECT DATEADD(HH, 1, GETDATE()) UNION ALL
SELECT DATEADD(HH, 2, GETDATE()) UNION ALL
SELECT DATEADD(DAY, 1, GETDATE()) UNION ALL
SELECT DATEADD(HH, 26, GETDATE()) UNION ALL
SELECT DATEADD(DAY, 6, GETDATE())

SELECT ID, EntryTime FROM (
SELECT ID, EntryTime, ROW_NUMBER() OVER (PARTITION BY CAST(EntryTime AS DATE) ORDER BY EntryTime ASC) AS RowNumber FROM #TestDatePartition
) SubTable
WHERE SubTable.RowNumber = 1

There are a couple of ways to do this one:

SELECT
    my_id,
    entry_date,
    x,
    y,
    z
FROM
    (
        SELECT
            DATEPART(DAYOFYEAR, entry_date) AS day_of_year,
            MIN(entry_date) AS first_of_day
        FROM
            My_Table
        WHERE
            entry_date > DATEADD(dy, -1 * @num_days_back, GETDATE())
        GROUP BY
            DATEPART(DAYOFYEAR, entry_date)
    ) SQ
INNER JOIN My_Table MT ON
    MT.entry_date > DATEADD(dy, -1 * @num_days_back, GETDATE()) AND
    MT.entry_date = SQ.first_of_day

Or, alternatively

SELECT
    my_id,
    entry_date,
    x,
    y,
    z
FROM
    My_Table MT1
LEFT OUTER JOIN My_Table MT2 ON
    MT2.entry_date > DATEADD(dy, -1 * @num_days_back, GETDATE()) AND
    DATEPART(DAYOFYEAR, MT2.entry_date) = DATEPART(DAYOFYEAR, MT1.entry_date) AND
    MT2.entry_date < MT1.entry_date
WHERE
    MT1.entry_date > DATEADD(dy, -1 * @num_days_back, GETDATE()) AND
    MT2.my_id IS NULL

I haven't tested these, so let me know if you run into any issues. Also, if you have two rows at the start of the day with identical entry_date values then these queries will both return both rows.