4

Question: how to find last row_number in a table in SQL Server? For example, I have a table named Employee without any ID and don't wanna add any identity column.

EmpName | EmpSalary
--------+----------
Amit    | 5000
Sumit   | 4500
Ajeet   | 5600

I tried this:

SELECT 
    ROW_NUMBER() OVER(ORDER BY EmpName) row_no 
FROM
    Employee

but it returns this:

row_no
1
2
3

but, if 3 is the last row_no, then I want:

row_no
3

now, please help me to solve this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Siddhartha Goswami
  • 471
  • 7
  • 11

2 Answers2

2
With cte as
(
SELECT ROW_NUMBER() OVER(ORDER BY EmpName) row_no from Employee 
)
Select max(row_no) from cte

Use a cte

Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20
  • @SiddharthaGoswami If you feel this answer helped solved your issue, please mark it as 'accepted' by clicking the green check mark. This will help the community to keep the focus on unanswered questions. – Lahiru Jayaratne Jun 06 '16 at 04:43
1

You can use this query:

SELECT TOP 1 row_no FROM
(
   SELECT ROW_NUMBER() OVER(ORDER BY EmpName) row_no FROM Employee
)AS Emp ORDER BY row_no DESC
RBT
  • 24,161
  • 21
  • 159
  • 240