Difficulty with a select - 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)

Difficulty with a select

Author

Topic

pharvey
Starting Member

20 Posts

Posted - 2008-10-22 : 07:18:54

Activity_PK Request_FK DateCreated
176 17 20/10/2008 5:03:27 PM
188 17 20/10/2008 6:00:16 PM
189 17 22/10/2008 9:47:27 AM
190 18 22/10/2008 9:49:47 AM
191 18 22/10/2008 9:50:14 AM

I have a table named tblActivity with those 3 fields. I would like to select for each Request_PK the last activity. The last activity is determined by the most recent date(DateCreated).

The results should be:
Activity_PK Request_FK DateCreated
189 17 22/10/2008 9:47:27 AM
191 18 22/10/2008 9:50:14 AM

I don't think that's a difficult one but i forgot how to do it.

Please did someone can help me!

Cheers,

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-10-22 : 07:31:01

declare @TEST table
(Activity_PK int,
Request_FK int,
DateCreated varchar (30)

)

INSERT INTO @TEST

SELECT 176, 17, '20/10/2008 5:03:27 PM' UNION ALL
SELECT 188, 17, '20/10/2008 6:00:16 PM' UNION ALL
SELECT 189, 17, '22/10/2008 9:47:27 AM' UNION ALL
SELECT 190, 18, '22/10/2008 9:49:47 AM' UNION ALL
SELECT 191, 18, '22/10/2008 9:50:14 AM'
Select * from (
SELECT Activity_PK,Request_FK,DateCreated, row_number() over (Partition by Request_FK order by DateCreated desc)AS RID
FROM @TEST )Temp where RID=1

pharvey
Starting Member

20 Posts

Posted - 2008-10-22 : 07:40:39

Is it possible to do it in another way than creating a temp object? Since tblActivity will become huge it would be really slow to do it...

Cheers,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 07:58:47

row_number() is not available in SQL Server 2000.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 08:03:52

select max(Activity_PK) as Activity_PK,Request_FK,max(DateCreated) as DateCreated
from tblActivity group by Request_FK

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 08:10:28

In this moment I wanted to post my solution:
Select * from tblActivity T
where DateCreated =
(select max(DateCreated) from tblActivity T2
where T2.Request_FK=T.Request_FK )

But madhi's solution looks much better!

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

pharvey
Starting Member

20 Posts

Posted - 2008-10-22 : 09:29:04

Thank you guys,
madhivanan your solution is quick, i was in the good track but i did not think to put the max in the pk.

Great solution!
Cheers,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 09:44:16

quote:
Originally posted by pharvey

Thank you guys,
madhivanan your solution is quick, i was in the good track but i did not think to put the max in the pk.

Great solution!
Cheers,

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources