CODEWITHSUNDEEP
row-number
Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

row number always return 1 for each row in sql server

(SELECT 
    (SELECT ROW_NUMBER() OVER (order by t.NotificationID)) as  RowNumber,
            [NotificationID],[ProjectID],[TeamMemberID],[OperationType],
            [Hours],[Occurance],[Period],[NotificationText],
            [NotificationRecipientIDs],[NotificationRecipientClienitsIDs]

       FROM tblIA_Notifications t
       WHERE IsDeleted = 0 AND IsActive = 1
    )

The above query always returns rownumber 1 for each row. When I use the select statement outside, its problem. Otherwise if I remove the outer select statement its fine.

I don't understand the behavior.

Upvotes: 1

Views: 5867

Answers (2)

Praloy Das
Praloy Das

Reputation: 325

Try this...

SELECT ROW_NUMBER() OVER (order by T.COLUMN_NAME) as RowNumber FROM [dbo].[TABLE_NAME] T

Upvotes: 2

AKD
AKD

Reputation: 3964

Uou are getting row_number 1 for each row because you are selecting the Row_Number for each row
try this--->

SELECT        ROW_NUMBER() OVER (order by t.NotificationID) as RowNumber,
              [NotificationID],
              [ProjectID],
              [TeamMemberID],
              [OperationType],
              [Hours],
              [Occurance],
              [Period],
              [NotificationText],
              [NotificationRecipientIDs],
              [NotificationRecipientClienitsIDs]
    FROM      tblIA_Notifications    t 
    WHERE     IsDeleted    =    0 
    AND       IsActive = 1

Upvotes: 4

Related Questions