CODEWITHSUNDEEP
sqlsql-server
George2
George2

Reputation: 45801

how to write select statement like this?

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

Upvotes: 1

Views: 465

Answers (7)

Colin
Colin

Reputation: 10638

SELECT
TOP 1 * FROM table WHERE VALUE = 10 
ORDER BY createtime

Upvotes: -1

John Hyland
John Hyland

Reputation: 6872

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

Upvotes: 2

Jeff Sternal
Jeff Sternal

Reputation: 48673

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

Upvotes: 5

OneSHOT
OneSHOT

Reputation: 6953

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

Upvotes: 2

Aaron Alton
Aaron Alton

Reputation: 23236

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425813

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

Upvotes: 3

Arjan Einbu
Arjan Einbu

Reputation: 13692

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

Upvotes: 9

Related Questions