2

How can I out how many rows I obtained after execution?

My query is:

SELECT a.Emp,b.orders 
from table as a inner join table1 b 
on a.ID = B.ID

How do I find the number of rows returned in the above join?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
user3203331
  • 429
  • 2
  • 7
  • 23

2 Answers2

6

You either have to use SELECT COUNT(*) ... with the same condition or add a column with the row-count via ROW_NUMBER function:

SELECT a.Emp,b.orders, RN = ROW_NUMBER () OVER (ORDER BY a.Emp,b.orders) 
FROM table as a inner join table1 b on a.ID=B.ID

...or use @@ROWCOUNT after the select.

Instead of ROW_NUMBER it's easier to use COUNT(*) OVER ( Order By ...) where each row contains the same total-count whereas ROW_NUMBER would return a sequential number where only the last record(acc. to the ORDER BY) would have the total-count.

So what Aaron has already meantioned in his answer.

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Well, the `ROW_NUMBER()` function will be applied in that order, but the result isn't guaranteed to be returned in that order without a matching `ORDER BY` on the outer query, and especially if there is a *different* `ORDER BY`. How do they find the largest `ROW_NUMBER()` value? – Aaron Bertrand Jan 28 '14 at 21:12
  • @AaronBertrand: You're right, `COUNT(*)OVER` is much easier(edited). – Tim Schmelter Jan 28 '14 at 21:19
6
-- statement here
SELECT @@ROWCOUNT;

You can also get it on every row of the statement, but of course this is a little more expensive, e.g.

SELECT x, y, z, COUNT(*) OVER() FROM ...
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490