Get last two updated rows from foreign key table

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 2005 Forums

Transact-SQL (2005)

Get last two updated rows from foreign key table

Author

Topic

dsvick
Starting Member

6 Posts

Posted - 2011-11-15 : 14:52:34

Not sure if I worded the subject properly or not...

I have two table with structures like this:

document
docID int
overallStatus int

docStatus
docID (primary key is in document table)
intermediateStatusType int
statusDate datetime
userID int

The status table can contain any of several different intermediate status type (created, viewed, cancelled, emailed, etc). What I need to do is get the most recent occurrences of two of those intermediate status types, the status date, and the person that did it for all documents that have a specific overall status.

I know I can do this on the web page by simply looping through all the documents that have the overall status and getting the most recent updates of the type I need but his would require one query to get all the documents then two more for each document. I'm sure there is a way to get this all done in one query but, for the life of me, I can't figure it out.

If anyone can point me in the right direction I'd greatly appreciate it. If this isn't enough enough let me know and I'll post more.

Thanks

Dave

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-15 : 19:51:57

I'm not sure if this matches your requirements; if not, pease clarify.[CODE]declare @OverallStatus int = 1 -- Set this value to your desired status

select
a.DocID,
a.intermediateStatusType,
a.statusDate,
a.UserID
from (
select
d.DocID,
s.intermediateStatusType,
s.statusDate,
s.UserID,
ROW_NUMBER() over (partition by d.DocID order by StatusDate DESC) rn
from
Document d
inner join
DocStatus s
on s.DocID = d.DocID
where
d.OverallStatus = @OverallStatus
) a
where
a.rn <= 2[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 01:06:10

can same docid have multiple records in docStatus with same intermediateStatusType ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dsvick
Starting Member

6 Posts

Posted - 2011-11-16 : 09:41:01

Bustaz

Thanks for the post, I'm checking out your code now to see how it will work me, I'd never seen ROW_NUMBER before so that looks promising. If I can't get it all in one row for each document then having just two rows would let me do it without having to loop through a bunch of rows I don't need.

visakh16
Yes, there can be multiple entries for one document with the same intermediate type. But the one status always immediately follows the other so the ROW_NUMBER solution will work since there can't be anything in between the two status events.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 12:25:57

quote:
Originally posted by dsvick

Bustaz

Thanks for the post, I'm checking out your code now to see how it will work me, I'd never seen ROW_NUMBER before so that looks promising. If I can't get it all in one row for each document then having just two rows would let me do it without having to loop through a bunch of rows I don't need.

visakh16
Yes, there can be multiple entries for one document with the same intermediate type. But the one status always immediately follows the other so the ROW_NUMBER solution will work since there can't be anything in between the two status events.

Ok in that case you're good t go with posted solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources