how to write select statement like this?

Question

Hello everyone,

I am using SQL Server 2008. I have a table which is composed of three columns, ID as string type, createtime as DataTime type and status as int.

I want to select the record among all records whose status value is 10 and at the same time createtime is the most earlist. Any ideas how to write such query?

BTW: ID is clustered index and I also have index on createtime column.

thanks in advance, George

Answer 1

SELECT TOP 1 *
FROM table
WHERE status = 10
ORDER BY created

Answer 2

select top 1 ID,
             CreateTime,
             Status
from         SourceTable
where        Status      = 10
order by     CreateTime

Answer 3

select top 1 * from table where value = 10 order by createtime

Answer 4

SELECT  TOP 1 id, createtime, status
FROM    mytable
WHERE   status = 10
ORDER BY
        createtime

Answer 5

How about this?

;WITH OrderedRows
AS
(SELECT ID, CreateTime, Status, ROW_NUMBER() OVER (ORDER BY CreateTime ASC) AS RowNumber
FROM MyTable
WHERE Status = 10)
SELECT * FROM OrderedRows
WHERE RowNumber = 1

Answer 6

I'm not familiar with SQL Server, specifically, but you should be able to do it with a subselect:

SELECT *
FROM Table t1
WHERE status = 10 AND createtime = (
    SELECT min(createtime)
    FROM Table t2
    WHERE status = 10
);

Answer 7

I prefer Arjan's answer but if you had more criteria with the "earliest created row" part of the select then i would look at subqueries e.g.

SELECT *
FROM table
WHERE status = 10 
AND created = (SELECT MIN(created)
               FROM table
               WHERE status = 10))

While this essentially runs 2 queries and is un-necessary for your requirements it gives you more control if you have more criteria to work with.

HTH

OneSHOT