CODEWITHSUNDEEP
c#sqlsql-server-ce
Jayesh Babu
Jayesh Babu

Reputation: 1449

How can I find the row number of a particular row?

I have a database table like this:

enter image description here

I want to get the the row number of the second row. I use the following code:

SELECT ROW_NUMBER() OVER(ORDER BY Name) From Deposit WHERE Name='Murali'

But, its not working. Whats wrong with the code? Thanks in advance.

Upvotes: 3

Views: 2048

Answers (3)

Darin Dimitrov
Darin Dimitrov

Reputation: 1038830

The ROW_NUMBER function returns the row number in the resulting dataset.

In your query you restricted the results to only those whose name is Murali. Since you have only one such record, it is normal that it will return 1.

In SQL there's no such notion as row number. Table rows do not have an order. The notion of order only makes sense when you make a SQL query. Without SQL query you simply cannot talk about order and row numbers.

It appears that you need to introduce some order number for each user. The correct way to implement this is to add an Order column to your Deposit table. Now in order to retrieve it you would use the following query:

SELECT [Order] From Deposit WHERE Name = 'Murali'

All that's left is make the Order column to autoincrement and you are good to go. Everytime a new record is inserted the value will be automatically incremented. So there you go, now you have an order which represents the order in which the records have been inserted into the table. You now have context.

Upvotes: 7

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

WITH TempTable AS
    (
        SELECT Name,ROW_NUMBER() OVER (ORDER BY Name) AS 'RowNumber'
        FROM Deposit
    ) 
    SELECT RowNumber,Name
    FROM TempTable 
WHERE Name='Murali'

Upvotes: 0

Dimitar Dimitrov
Dimitar Dimitrov

Reputation: 15148

Perhaps something like this (if I understood you correctly):

SELECT Q.RN FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY Name) AS RN, * From Deposit
) AS Q
WHERE Q.Name = 'Murali'

Upvotes: 2

Related Questions