3

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.

Jayesh Babu
  • 1,389
  • 2
  • 20
  • 34

3 Answers3

7

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.

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • is there any way to find the row number..? – Jayesh Babu Dec 31 '13 at 09:33
  • You need to first define what *row number* means. Because there's no such notion in SQL as I already explained in my answer. – Darin Dimitrov Dec 31 '13 at 09:33
  • my aim here is that when the user give the input as "Murali", it should return 2. Is there any way..? – Jayesh Babu Dec 31 '13 at 09:37
  • 1
    But why it should return 2? This doesn't make any sense. If you want to introduce such notion simply add an `Order` column to your table where you will store this information. – Darin Dimitrov Dec 31 '13 at 09:38
  • Then all you need is an `Order` column. – Darin Dimitrov Dec 31 '13 at 09:40
  • sir, the code you mentioned above has an error.. Error shown is "Missing or incomplete SELECT clause." – Jayesh Babu Dec 31 '13 at 09:47
  • Maybe because `Order` is a reserved word. You should rap in in angle brackets: `[Order]`. I have updated my answer. – Darin Dimitrov Dec 31 '13 at 09:50
  • still there is an error.. Error shown is: "The column name is not valid.[Node name(if any)=,column name=Order]" – Jayesh Babu Dec 31 '13 at 09:54
  • That's probably because you didn't add such column to your database table. – Darin Dimitrov Dec 31 '13 at 10:03
  • if i use column name as Order, will the entries in it will automatically increment..? Is there any special query to add 'Order' column..? – Jayesh Babu Dec 31 '13 at 10:06
  • Well not automatically, but when creating this column you could make it an AutoIncrement and it will work. Everytime you insert a new record its value will increment. Which I guess is exactly what you need here. – Darin Dimitrov Dec 31 '13 at 10:07
  • yes, thats exactly what i want.. So, is it same as primary key column..? – Jayesh Babu Dec 31 '13 at 10:09
  • Not exactly. The primary key column is for internal use only. It has absolutely no business meaning to your application. Whereas the `Order` column has a very special business meaning. But technically, yeah, could be considered the same. – Darin Dimitrov Dec 31 '13 at 10:10
  • will you please tell me the query to add Order column to the above table..? – Jayesh Babu Dec 31 '13 at 10:16
  • The same way you added the other columns :-) For example you could use the SQL Server Management Studio. – Darin Dimitrov Dec 31 '13 at 10:16
  • i mean, how can i make it autoincrement..? – Jayesh Babu Dec 31 '13 at 10:22
  • i have tried this before.. the problem with this is that when i insert 3 tuples, the order column will increment upto 3. but, when i delete one tuple and again insert another tuple, order of newly inserted tuple will be 4, not 3.. is there any way to solve this..? – Jayesh Babu Dec 31 '13 at 10:28
  • When you delete a row, you could use the `DBCC` command in SQL server to reseed the value of the counter: http://stackoverflow.com/a/510132/29407 – Darin Dimitrov Dec 31 '13 at 10:31
  • DBCC is not supported by the server iam using,ie,sql server compact edition.. is there any other that i can use instead of DBCC..? – Jayesh Babu Dec 31 '13 at 14:16
2

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'
Dimitar Dimitrov
  • 14,868
  • 8
  • 51
  • 79
0

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'
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115