Get last record in sequence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get last record in sequence

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-11 : 11:11:03

I have this query:
select top 50 vacancy_pointer, person_pointer, creationdate, type
from vacancytransaction
where vacancy_pointer <> 0 AND person_pointer <> 0
order by vacancy_pointer, person_pointer, creationdate

This is part of the result:

2 33172 2002-02-07 00:00:00.000 FCV
2 37504 2002-02-07 00:00:00.000 FCV
2 62027 2002-02-07 00:00:00.000 FCV
3 1806 1998-12-22 00:00:00.000 FCV
3 1806 1999-01-07 00:00:00.000 XXX
3 39461 1999-01-18 00:00:00.000 FCV
3 40037 1999-01-08 00:00:00.000 FCV
3 42213 1999-01-07 00:00:00.000 FCV
3 54631 1999-02-25 00:00:00.000 FCV
4 326 1999-01-13 00:00:00.000 FCV
4 4408 1999-01-19 00:00:00.000 FCV
4 13402 1999-01-21 00:00:00.000 FCV
4 21178 1999-01-08 00:00:00.000 FCV
4 21538 1999-01-08 00:00:00.000 FCV
4 22598 1999-01-11 00:00:00.000 FCV
4 26566 1999-01-08 00:00:00.000 FCV
4 29987 1999-01-14 00:00:00.000 JFL
4 30785 1999-01-12 00:00:00.000 FCV
4 32201 1999-01-08 00:00:00.000 FCV
4 35414 1999-01-08 00:00:00.000 FCV
4 36554 1999-01-08 00:00:00.000 FCV
4 44333 1999-01-12 00:00:00.000 FCV
4 48639 1999-01-08 00:00:00.000 FCV
4 59222 1999-01-08 00:00:00.000 FCV
4 62236 1999-01-20 00:00:00.000 FCV
4 64854 1999-01-25 00:00:00.000 FCV
4 64990 1999-01-12 00:00:00.000 FCV
5 1579 1999-01-05 00:00:00.000 FCV
5 2216 1998-12-22 00:00:00.000 FCV
5 2216 1999-01-06 00:00:00.000 XXX
5 4877 1998-12-22 00:00:00.000 FCV
5 4877 1999-01-06 00:00:00.000 XXX

Look at the last for records as an example.
There you see 2 records for a vacancy_pointer & person_pointer pair.
There could be any number of records for such a pair. but I only want to return the one with the most recent creationdate.
Can that be done in one statement?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-11 : 11:20:32

try this:


select top 50 t1.vacancy_pointer, t1.person_pointer, t1.creationdate, t1.type 
from vacancytransaction t1
	join (select vacancy_pointer, person_pointer, max(creationdate) as creationdate 
		from vacancytransaction group by vacancy_pointer, person_pointer
	) t2 on t1.vacancy_pointer = t2.vacancy_pointer and 
		t1.person_pointer = t2.person_pointer and 
		t1.creationdate = t2.creationdate
where t1.vacancy_pointer <> 0 AND t1.person_pointer <> 0
order by t1.vacancy_pointer, t1.person_pointer, t1.creationdate

Go with the flow & have fun! Else fight the flow