Discussion:
How do I find the oldest record in a table
(too old to reply)
Kjell Arne Johansen
2005-05-14 08:57:08 UTC
Permalink
Hi

In one of my tables in the MS SQL database all records are time stamped.
I want to know what is the oldest record.

I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
-and it works because I know the data in the table is newer than 1990 but is
there a more intelligent way of doing it?
(I also think it work because the primary key has an ascending sorting
order).

Thanks for Your help.

Regards
Kjell Arne Johansen
Itzik Ben-Gan
2005-05-14 09:18:34 UTC
Permalink
Is the time stamp unique in your table?

If so, use:

SELECT TOP 1 * FROM <table> ORDER BY ts DESC

Or

SELECT * FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1)

If it's not unique, use:

SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC

Or

SELECT * FROM T1
WHERE key =
(SELECT MAX(key) FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1))
--
BG, SQL Server MVP
www.SolidQualityLearning.com
Post by Kjell Arne Johansen
Hi
In one of my tables in the MS SQL database all records are time stamped.
I want to know what is the oldest record.
I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
-and it works because I know the data in the table is newer than 1990 but
is there a more intelligent way of doing it?
(I also think it work because the primary key has an ascending sorting
order).
Thanks for Your help.
Regards
Kjell Arne Johansen
Kjell Arne Johansen
2005-05-14 10:58:53 UTC
Permalink
Thank You for your examples.
The time is not unique. I will have to use a combination of time and two
other fields.


Regards
Kjell Arne
Post by Itzik Ben-Gan
Is the time stamp unique in your table?
SELECT TOP 1 * FROM <table> ORDER BY ts DESC
Or
SELECT * FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1)
SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC
Or
SELECT * FROM T1
WHERE key =
(SELECT MAX(key) FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1))
--
BG, SQL Server MVP
www.SolidQualityLearning.com
Post by Kjell Arne Johansen
Hi
In one of my tables in the MS SQL database all records are time stamped.
I want to know what is the oldest record.
I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
-and it works because I know the data in the table is newer than 1990 but
is there a more intelligent way of doing it?
(I also think it work because the primary key has an ascending sorting
order).
Thanks for Your help.
Regards
Kjell Arne Johansen
CBretana
2005-05-14 14:27:01 UTC
Permalink
Then order by all three columns, desc... and use Top 1
Post by Kjell Arne Johansen
Thank You for your examples.
The time is not unique. I will have to use a combination of time and two
other fields.
Regards
Kjell Arne
Post by Itzik Ben-Gan
Is the time stamp unique in your table?
SELECT TOP 1 * FROM <table> ORDER BY ts DESC
Or
SELECT * FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1)
SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC
Or
SELECT * FROM T1
WHERE key =
(SELECT MAX(key) FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1))
--
BG, SQL Server MVP
www.SolidQualityLearning.com
Post by Kjell Arne Johansen
Hi
In one of my tables in the MS SQL database all records are time stamped.
I want to know what is the oldest record.
I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
-and it works because I know the data in the table is newer than 1990 but
is there a more intelligent way of doing it?
(I also think it work because the primary key has an ascending sorting
order).
Thanks for Your help.
Regards
Kjell Arne Johansen
Loading...