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.
| Author |
Topic |
|
omkar786
Starting Member
1 Post |
 Posted - 2011-12-22 : 07:39:37
|
| Hi,I have to get count of the rows returned from an inner query that uses TOP clause, as in below query.Used the below query but I am getting NumOccurences as 25 when the inner query returns only 5 rows when @LastNumJobs = 10.SELECT COUNT(*) AS NumOccurences FROM JOB_HISTORY T1 WITH (NOLOCK), (SELECT TOP(@LastNumJobs) JOB_ID,DUE_DATE FROM JOB_HISTORY WITH (NOLOCK) WHERE ID = @Id AND VERSION = @PVersion AND PERFORMED IN (1,3) AND NODE_ID = @NodeId ORDER BY DUE_DATE DESC) T2 WHERE T1.ID = @Id AND T1.VERSION = @PVersion AND T1.NODE_ID = @NodeId AND T1.PERFORMED IN (1,3) AND T1.JOB_ID = T2.JOB_ID Please let me know if this is the right way or suggest any alternate.Requirement is to get count of rows out of certain number of rows being fetched, for example there are 5 rows returned for the inner query but the input value of @LastNumJobs is 10. I need to get the count of rows returned from inner query eqaual to same number of @LastNumJobs or less than that.Thanks in advanceOmkar |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-22 : 09:15:12
|
| Your description of the issue suggests that there is more than one row in the JOB_HISTORY table for a given JOB_ID. Can you describe with an example what you are trying to calculate? For example, if you are trying to get the Top 5 jobs with the most recent due dates, there are simpler ways to do it. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
How to get count of the rows returned ?