CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
kfir ezer
kfir ezer

Reputation: 179

I get an error when I try to use ROW_NUMBER(), why?

I'm trying to write something like this:

string AT2 = string.Format("UPDATE User_Info SET Admin = 1 WHERE (Row_Number = N'{1}'+N'{0}')", x, 1);

with this as well above it:

string cmdStr = "SELECT Firstname, Lastname, Username, Password, Email, Phone, Admin, id, ROW_NUMBER() as 'Row_Number' FROM User_Info";

I get this error:

The function 'ROW_NUMBER' must have an OVER clause.

Does anyone have any idea why? so I need to add something in my table..? or write it differently?

My table:

CREATE TABLE [dbo].[User_Info] 
(
    [Username]  NVARCHAR (50) NOT NULL,
    [Password]  NVARCHAR (50) NOT NULL,
    [Firstname] NVARCHAR (50) NOT NULL,
    [Lastname]  NVARCHAR (50) NOT NULL,
    [Email]     NVARCHAR (50) NOT NULL,
    [Country]   NVARCHAR (50) NOT NULL,
    [Phone]     NVARCHAR (50) NOT NULL,
    [Gender]    NVARCHAR (50) NOT NULL,
    [Admin]     INT           NULL,
    [id]        INT IDENTITY (1, 1) NOT NULL,

    PRIMARY KEY CLUSTERED ([Username] ASC)
);

Upvotes: 1

Views: 117

Answers (1)

Gnyasha
Gnyasha

Reputation: 684

string cmdStr = "SELECT Firstname, Lastname, Username, Password, Email, Phone, Admin, id, ROW_NUMBER() OVER (ORDER BY Firstname) FROM User_Info";

You are missing the ROW_NUMBER() OVER (ORDER BY Firstname) OVER() clause.

If you want to order by id use it as ROW_NUMBER() OVER (ORDER BY Id) so the rows will increase with respect to Id ascending the same goes for any row you want to base your ROW_NUMBER clause.

So the update statement may like this.

UPDATE User_Info SET Admin = 1 WHERE id=

(SELECT ID FROM (
(SELECT Firstname, Lastname, Username, Password, Email, Phone, Admin, id, ROW_NUMBER() OVER (ORDER BY Firstname) ROWNUMBER FROM User_Info)) b WHERE b.ROWNUMBER=N'{1}'+N'{0}')

Upvotes: 2

Related Questions