tags:

views:

204

answers:

7

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

+9  A: 
SELECT TOP 1 *
FROM table
WHERE status = 10
ORDER BY created
Arjan Einbu
as a good rule of thumb, never use * (all) ... always specify the fields
balexandre
@balexandre: You're right... I know... :)
Arjan Einbu
For those which is too lazy to write all fields one by one let write statement select * from table then mark text and right click "Design Query in Editor" after that only click OK, Visual studio will enumerate names of all filed in selected statement
adopilot
+5  A: 
select top 1 ID,
             CreateTime,
             Status
from         SourceTable
where        Status      = 10
order by     CreateTime
Jeff Sternal
A: 

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

Colin
orderby what? Your statement is incomplete and has invalid syntax.
Matt Grande
sorry, had createtime in html tag style, was not shown in answer..
Colin
+3  A: 
SELECT  TOP 1 id, createtime, status
FROM    mytable
WHERE   status = 10
ORDER BY
        createtime
Quassnoi
A: 

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
Aaron Alton
Why was this downvoted without providing an explanation? This is a correct answer.
AlexKuznetsov
+2  A: 

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
);
John Hyland
+2  A: 

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

OneSHOT