CODEWITHSUNDEEP
sqlsql-server
Gerald
Gerald

Reputation: 1083

sql query to process top count first

I have a problem with my sql query below:

select * from Assignment as a 
inner join #resource as r 
    on a.ResourceID = r.ResourceID
inner join Resource as pr 
   on pr.ResourceID = r.ResourceID
inner join #workitem as uw 
   on uw.ReferenceID = pr.ResourceSecurityUserID
where uw.WorkQueueID = some id here
   and uw.WorkStatusID = some status id here

What it does is get all the assignment of a resource on the Assignment table using the id from the temp table #resource and look at the Resource table and using a referenceid from the #workitem table.

The purpose of the #workitem temp table is to queue all my processes. This is my requirement. Is it possible that I only want top 100 from the #workitem temp table in order to process the top 100 first.

Thanks in advance.

Upvotes: 0

Views: 65

Answers (2)

atom.gregg
atom.gregg

Reputation: 1007

When you join to the workitem temporary table, make that as an inner join, and produce a row_number in there so you can limit the number of records joined to.

select * from Assignment as a 
inner join #resource as r 
    on a.ResourceID = r.ResourceID
inner join Resource as pr 
   on pr.ResourceID = r.ResourceID
inner join (
  select *, row_number() over () as rn
  from #workitem uw
) as uw on uw.ReferenceID = pr.ResourceSecurityUserID and uw.rn <= 100
where uw.WorkQueueID = some id here
   and uw.WorkStatusID = some status id here

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try somthing like this:-

SELECT * FROM Assignment AS a 
INNER JOIN #resource AS r ON a.ResourceID = r.ResourceID
INNER JOIN Resource AS pr ON pr.ResourceID = r.ResourceID
INNER JOIN (SELECT TOP 100 * FROM #workitem) as uw ON uw.ReferenceID = pr.ResourceSecurityUserID
WHERE uw.WorkQueueID = some id here
AND uw.WorkStatusID = some status id here

Hopefully this will work and helpful to you.

Upvotes: 1

Related Questions