SQL Query for lowest time rows

Question

I have a table with one DateTime column. I want to find the rows with lowest time which their times are more than a variable myTime. How can I do that? What kind of index will increase the performance?

Answer 1

SELECT DateTime1
FROM Table1
WHERE DateTime1 = (
    SELECT MIN(DateTime1)
    FROM Table1
    WHERE DateTime1 > @myTime);

or:

SELECT T1.DateTime1
FROM Table1 AS T1
JOIN (
    SELECT MIN(DateTime1) AS DateTime1
    FROM Table1
    WHERE DateTime1 > @myTime) AS T2
ON T1.DateTime1 = T2.DateTime1;

Run both to see which is fastest. Use an index on DateTime1.

Test data to check it works:

CREATE TABLE Table1 (DateTime1 NVARCHAR(100) NOT NULL);
INSERT INTO Table1 (DateTime1) VALUES
('2010-02-01 17:53'),
('2010-02-01 17:55'),
('2010-02-01 17:55'),
('2010-02-01 17:56');

Answer 2

DECLARE @temp datetime

SET @temp = (SELECT GETDATE())

SELECT MyCol, MyCol2 FROM MyTable WHERE MyColWithDate > @temp ORDER BY MyColWithDate ASC

Regarding the index it depends can you tell us more info about what you are trying to do. Otherwise you could index MyColWithDate for the above example.

Answer 3

Something like this then...

DECLARE @temp datetime

SET @temp = (SELECT GETDATE())

Select myCol from MyTable where MyColWithDate IN (SELECT top 1 MyCol, MyCol2 FROM MyTable WHERE MyColWithDate > @temp ORDER BY MyColWithDate ASC)

or I'd probably go with the MIN

Select myCol from MyTable where MyColWithDate IN (SELECT MIN(MycolWithDAte) FROM MyTable WHERE MyColWithDate > @temp GROUP BY MyColWithDate)

Answer 4

Using TOP (SQL Server 2000+)

---

SELECT t.*
  FROM TABLE t
 WHERE t.date_time_col = (SELECT TOP 1
                                 x.date_time_col
                            FROM TABLE x
                           WHERE CONVERT(hh, x.date_time_col, 8) < @myTime
                        ORDER BY x.date_time_col)

Using MIN

---

SELECT t.*
  FROM TABLE t
 WHERE t.date_time_col = (SELECT MIN(x.date_time_col)
                            FROM TABLE x
                           WHERE CONVERT(hh, x.date_time_col, 8) < @myTime)

Neither will use an index, assuming one exists on the DATETIME column, because of the use of a function - the data type is being changed.