get record with earliest datetime value

[hharry]

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanks

[SQLJunkie]

How about doing this:
1: Change Row_ID from NULL to NOT NULL
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NOT NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

2: Add value for column ROW_ID in INSERT:
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 1)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)

3: Use correct table name in SELECT - from REQUEST_LOG to REQUESTS
SELECT TOP 1 ROW_ID FROM REQUESTs WHERE STATUS = 0 ORDER BY
REQUEST_DATE
On Feb 9, 9:59 am, "hharry" <[e-mail]:

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanks

[hharry]

apologies for the typos

what i should have asked is this:

Is TOP applied after the ORDER BY or before...can someone confirm
this ?

[Plamen Ratchev]

Yes, TOP is applied after the result set rows are ordered with ORDER BY.

Regards,

Plamen Ratchev
http://www.SQLStudio.com