Finding the most recient datetime

[JohnnyRuin]

Hi,

My table has int Field1 and DateTime dtModificationTime. I maintain a
history of changes to the table by inserting a new record with a new
dtModificationTime. I need to be able to find the last inserted
record. It's easy to do Select * from table where field1=x ORDER BY
dtModificationTime. That works, but it hits all the records that
match the field. I need to come back with an exact hit. Can someone
point me in the right direction?

Thanks.

[manrajshekar]

Hi Johnny,

Use this

select top 1 * from table where field = X order by update_dt desc.

This would solve your problem.

Regards,
-Manoj Rajshekar

[Simon Hayes]

JohnnyRuin wrote:

Hi,

My table has int Field1 and DateTime dtModificationTime. I maintain a
history of changes to the table by inserting a new record with a new
dtModificationTime. I need to be able to find the last inserted
record. It's easy to do Select * from table where field1=x ORDER BY
dtModificationTime. That works, but it hits all the records that
match the field. I need to come back with an exact hit. Can someone
point me in the right direction?

Thanks.

See "Limiting Result Sets Using TOP and PERCENT", SET ROWCOUNT and
SELECT in Books Online:

select top 1 Field1, dtModificationTime
from dbo.MyTable
order by dtModificationTime desc

You should probably also consider what happens when you have multiple
rows with the same value in dtModificationTime.

Simon

[Jerry Spivey]

Use the MAX function.

HTH

Jerry
"JohnnyRuin" <sc**********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi,

My table has int Field1 and DateTime dtModificationTime. I maintain a
history of changes to the table by inserting a new record with a new
dtModificationTime. I need to be able to find the last inserted
record. It's easy to do Select * from table where field1=x ORDER BY
dtModificationTime. That works, but it hits all the records that
match the field. I need to come back with an exact hit. Can someone
point me in the right direction?

Thanks.