Posted by SQLJunkie on 02/09/07 15:25

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" <paulquig...@nyc.com> wrote:
> 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

[Back to original message]