Getting last (newest) one record (datetime column or id)

Hello everybody,

---------------

CREATE TABLE [T1] (
[IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[DateEvt] [datetime] NOT NULL,
[Value] [varchar] (10) NOT NULL ,
[DataX] [varchar] (10) NULL ,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[IDX]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
'0000000001', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
'0000000002', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
'0000000003', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
'0000000001', 'BBBBBBBBBB')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
'0000000001', 'CCCCCCCCCC')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
'0000000001', 'DDDDDDDDDD')
GO

---------------

and the question is:
In which fastes and best for the preformance way, get the last IDX of
specified Value.

I could do this like this:

---------------
declare @.nIDX numeric
declare @.sValue varchar(10)

select top 1 @.nIDX = IDX from T1
where Value = @.sValue
order by DateEVT desc
---------------

But I know, this is not fast (even if I have index on DateEVT field),
and I'm quite sure, that there is better way to get this IDX.
Anyway, this table can be big (like 20 milions records).

I could take the max of IDX, but is it a sure way?
Any help? Thanks in advance

Matik> declare @.nIDX numeric
> declare @.sValue varchar(10)
> select top 1 @.nIDX = IDX from T1
> where Value = @.sValue
> order by DateEVT desc

To optimize this query, consider creating a non-clustered index on Value and
using MAX. This index will cover the query becuase the clustered index
value (IDX) is also stored in the non-clustered index.

DECLARE @.nIDX numeric
DECLARE @.sValue varchar(10)
SELECT @.nIDX = MAX(IDX)
FROM T1
WHERE Value = @.sValue

Also, if you are using SQL 2000, consider bigint instead of numeric(18, 0).
This will save a little space.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matik" <marzec@.sauron.xo.pl> wrote in message
news:1102374422.534762.91540@.f14g2000cwb.googlegro ups.com...
> Hello everybody,
> ---------------
> CREATE TABLE [T1] (
> [IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [DateEvt] [datetime] NOT NULL,
> [Value] [varchar] (10) NOT NULL ,
> [DataX] [varchar] (10) NULL ,
> CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
> (
> [IDX]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
> '0000000001', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
> '0000000002', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
> '0000000003', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
> '0000000001', 'BBBBBBBBBB')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
> '0000000001', 'CCCCCCCCCC')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
> '0000000001', 'DDDDDDDDDD')
> GO
> ---------------
> and the question is:
> In which fastes and best for the preformance way, get the last IDX of
> specified Value.
> I could do this like this:
> ---------------
> declare @.nIDX numeric
> declare @.sValue varchar(10)
> select top 1 @.nIDX = IDX from T1
> where Value = @.sValue
> order by DateEVT desc
> ---------------
> But I know, this is not fast (even if I have index on DateEVT field),
> and I'm quite sure, that there is better way to get this IDX.
> Anyway, this table can be big (like 20 milions records).
> I could take the max of IDX, but is it a sure way?
> Any help? Thanks in advance
> Matik