SELECT TOP Issue - 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

Old Forums

CLOSED - General SQL Server

SELECT TOP Issue

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-17 : 08:26:36

Tom writes "I have two tables - tblCase and tblActivity. The activity table is an activity "log", related to tblCase by CaseID (one case will have many activities).

I need to generate a recordset that displays the last "X" activities for each case, sorted descending by date. "X" is a number designated by the user and needs to be passed in as a parameter in a stored proc, I would assume.

I think the best way to sum up what I'm trying to accomplish is to say that I need to perform a SELECT TOP for each case record. I can do it one case at a time, but I'm trying to get them all in one recordset! I'm relatively adept at SQL statements and stored procs, but I have no idea how to go about this!!

Please help if you can.

Thanks!
Tom
tom@personalpc.com"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-17 : 08:38:02

something like

select *
from tblActivity t1
where date in (select top x date from tblActivity t2 where t1.caseid = t2.caseid order by date desc)

as x is a variable you would have to do this in dynamic sql

declare @sql varchar(1000)
select @sql = 'select *
from tblActivity t1
where date in (select top ' + convert(varchar(20),@x) + ' date from tblActivity t2 where t1.caseid = t2.caseid order by date desc)'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-17 : 08:46:45

You need to calculate the Rank of each activity, per Case, based on the date in a descending manner:


SELECT A.CaseID, A.Activity, ... (other columns to return) ...,
  (SELECT count(*) 
   FROM Activities A2
   WHERE A.CaseID = A2.CaseID AND 
         A.Date >= A2.Date) Rank
FROM
  Activities A

Check it out, see how it works, make sure it makes sense, and make sure it is ranking the activities in the way you'd like.

Watch out for ties, though -- you must decide how to handle them. Do a search from my username and the work "Rank" in these forums for lots more posts about this and information.

Once you have that, if @N is the number of top rows to return, you can then say:


SELECT * FROM (above SQL) a
WHERE Rank <= @N
ORDER BY CaseID, Rank

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources