Getting the last record

[Gatorajc]

What is( if there is one ) the command for getting the last record? For example:

WorkObject Date ActionTaken Completed
5666 2/11/02 Created N
5666 3/2/02 indexed N
5666 3/7/02 routed N
5677 2/11/02 Created N
5677 3/2/02 indexed N
5677 3/7/02 Completed Y
and the list goes on and on. Over 500,000 records.
The last record of each workobject changes so it is not possible to sort or narrow it down by any other field. I need the last record for each WorkObject.

Thanks in advance for any help.

 

[edge2000]

Hi,

If you added a Identity column the last record would be the highest value identity column for the Workobject group ie

IdentityCol WorkObject
1 5666
2 5666
3 5666
4 5667

You can use the MAX(IdentityCol) in a select statement to find the last value.

Cheers Dave Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.

 

[Gatorajc]

Believe it or not the vendor who created the table did not put a unique field in the table. No primary key nothing at all. But someone else in another forum suggeted MAX() as well I will work with that for a while to see what happens.

Thanks

 

[SQLBill]

Try using MAX on your date column:

select max(date)
from tablename

-SQLBill

 

[pankajv]

SELECT WorkObject, MAX(Date)
FROM TableName
GROUP BY WorkObject

 

[Guest_imported]

You could order the query in reverse order and then just put TOP 1 in the select statement (right at the beginning)

IE

SELECT TOP 1 Primary_key_index, WorkObject ,Date , ActionTaken , Completed
FROM tablename
ORDER BY primary_key_index DESC

 

[SQLBill]

Judy2000,

Good suggestion, normally. But you missed his second post:

Believe it or not the vendor who created the table did not put a unique field in the table. No primary key nothing at all. But someone else in another forum suggeted MAX() as well I will work with that for a while to see what happens.

-SQLBill