CODEWITHSUNDEEP
sqlsql-serverperformancedatetime
Shayan
Shayan

Reputation: 2828

SQL Query for lowest time rows

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?

Upvotes: 1

Views: 1499

Answers (4)

Sam
Sam

Reputation: 7678

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)

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332681

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.

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838796

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');

Upvotes: 1

JonH
JonH

Reputation: 33163

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.

Upvotes: 0

Related Questions