ROW_NUMBER() in SQL SERVER

ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set.

 if the result changes, the ROW_NUMBER() will change.

select distinct ROW_NUMBER()over (order by sal desc ) as [Row Number],EMPNO from EMP

      Distinct will not work when we use ROW_NUMBER

Row Number           EMPNO

-------------------- ---------------------------------------

1                    7839

2                    7902

3                    7788

4                    7566

5                    7698

6                    7782

7                    7499

8                    7844

9                    7934

10                   7521

11                   7654

12                   7876

13                   7900

14                   7369

15                   7370

16                   7370

(16 row(s) affected)

select distinct ROW_NUMBER()over (order by sal desc ) as [Row Number],EMPNO from EMP where EMPNO>7800

Row Number           EMPNO

-------------------- ---------------------------------------

1                    7839

2                    7902

3                    7844

4                    7934

5                    7876

6                    7900

(6 row(s) affected)

select ROW_NUMBER()over(PARTITION by EMPNO

order by sal desc )

 as [Row_number],EMPNO ,SAL

 from

      EMP

where EMPNO>7800

Row_number           EMPNO                                   SAL

-------------------- --------------------------------------- ---------------------------------------

1                    7839                                    5000.00

1                    7844                                    1500.00

1                    7876                                    1100.00

1                    7900                                    950.00

1                    7902                                    3000.00

1                    7934                                    1300.00

----------------------------------------------------*********----------------------------------------------------

--------------------------------------*****************Endof example1------------------------------------------------

Example 2:

create table MyPeople(PersonID int,FamilyID int,FName varchar(100),LName varchar(100),DOB date)

insert into MyPeople values(1,1,'Raghu','Dasara','1982-05-07 03:00:00'),

                                           (2,1,'Chaithnay','Dasara','1987-10-07 15:00:00') ,

                                           (3,2,'Pathi','Dasara','1994-10-13 10:00:00'),

                                           (4,2,'Sudhar','Simha','1994-10-13 10:02:00'),

                                           (5,2,'Rahul','Ramghad','1987-05-07 19:00:00')  

SELECT

     [PersonID]

    ,[FamilyID]

    ,[FName]

    ,[LName]

    ,[DOB]

    ,ROW_NUMBER() over (ORDER BY DOB) AS Number

FROM

       MyPeople

Result of the above Query :

        PersonID       FamilyID       FName        LName    DOB        Number

1               1               Raghu      Dasara  1982-05-07  1

5               2               Rahul      Ramghad 1987-05-07  2

2               1               Chaithnay  Dasara  1987-10-07  3

3               2               Pathi      Dasara  1994-10-13  4

4               2               Sudhar     Simha   1994-10-13  5

SELECT

     [PersonID]

    ,[FamilyID]

    ,[FName]

    ,[LName]

    ,[DOB]

    ,ROW_NUMBER() over (ORDER BY DOB) AS Number

FROM

       MyPeople

ORDER BY

       PersonID

Result of the above Query :

PersonID       FamilyID       FName        LName   DOB         Number

1               1               Raghu      Dasara  1982-05-07  1

2               1               Chaithnay  Dasara  1987-10-07  3

3               2               Pathi      Dasara  1994-10-13  4

4               2               Sudhar     Simha   1994-10-13  5

5               2               Rahul      Ramghad 1987-05-07  2

SELECT

      [PersonID]

     ,[FamilyID]

     ,[FName]

     ,[LName]

     ,[DOB],

      ROW_NUMBER() over(PARTITION BY FamilyID,

                         CONVERT(NVARCHAR(25), DOB, 111) -- select SYSDATETIME()--2014-07-10 10:44:46.1160635

                         ORDER BY DOB ASC) TwinCode      --,select CONVERT(varchar(10),SYSDATETIME(),111) --2014/07/10

                                                                                              

  FROM [MyPeople]

       ORDER BY     PersonID

Result of the above Query :

PersonID       FamilyID       FName      LName   DOB         TwinCode

1               1             Raghu      Dasara  1982-05-07  1

2               1             Chaithnay  Dasara  1987-10-07  1

3               2             Pathi      Dasara  1994-10-13  1

4               2             Sudhar     Simha   1994-10-13  2

5               2             Rahul      Ramghad 1987-05-07  1