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.

 

[olded]

Hi:

How about:

select workobject, max(date) from wotab group by workobject

where wotab is the table name.

Regards,

Ed

 

[Gatorajc]

No go it says workobject can not be used it is not contained in the aggregate function.

 

[Gatorajc]

Sorry it did work but did not get the results we wanted.

 

[olded]

Gator:

How about creating a temp table and saving the max date in that. Then do a join on the temp table and your database table:

create temp table b (
workobject char(4),
ddate date
);
insert into b select workobject, max(date)
from wotab group by workobject;
select wotab.* from wotab, b where wotab.workobject = b.workobject and wotab.date = b.ddate;

Regards,

Ed

 

[angiole]

You might also try the following.
Assumming length of 10 for all columns

Select workobject,
substr(max(date||action||stat),1,10) as latest_wobj_date,
substr(max(date||action||stat),11,10) as latest_wobj_action,
substr(max(date||action||stat),21,10) as latest_wobj_stat
from table1
group by workobject.

This will return the latest record values for each workobject number found in the table.

 

[adam0101]

how about:
select * from table_name where date=(select max(date) from table_name)

p.s. I wouldn't recommend naming your column "date".

 

[tlbroadbent]

Try thsi.

Select
WorkObject, [Date],
ActionTaken, Completed
From table_name t
Where [date] =
(Select Max([date])
From table_name
Where WorkObject=t.WorkObject)

I also recommend avoinding the use of reserved words for object or column names. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Gatorajc]

Thank you everyone for all your help. Actually the column name is action_date, not just date. I just made it shorter for the sake of space.

Thank you all for your suggestions I will try all of them as time allows.