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

[Matik]

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

[Dan Guzman]

> 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" <ma****@sauron.xo.pl> wrote in message
news:11*********************@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