ROW_NUMBER function
ROW_NUMBER is a function which returns the ''sequential number of a row" from the Result set. As well as How many times that particular row has been repeated.
CREATE TABLE #Temp
(SNO INT,[Name] VARCHAR(25))
---This value has entered 5 times
INSERT INTO #Temp
(SNo,[Name])
VALUES(1,'Ramesh')
GO
-----This value entered has 3 times
INSERT INTO #Temp
(SNo,[Name])
VALUES(2,'Jagan')
GO
-----------This value entered has 3 times
INSERT INTO #Temp
(SNo,[Name])
VALUES(3,'Mohan')
go
SELECT * FROM #Temp
GO
SELECT *, ROW_NUMBER()OVER(PARTITION BY SNo ORDER BY [Name]DESC)
FROM #Temp
ROW_NUMBER function will tell us how many times that particular row has been repeated. If it shows last value as 5.That means that particular row is repeated 4 times.
If we want to give sequential number to values we can make small change in the Query.
Eg:
CREATE TABLE rows
([Name] VARCHAR(20))
GO
INSERT INTO rows
([Name])
VALUES('Mahesh')
GO
INSERT INTO rows
([Name])
VALUES('Mahesh')
GO
INSERT INTO rows
([Name])
VALUES('Mahesh')
go
SELECT ROW_NUMBER()OVER(ORDER BY [Name])AS Sno,[Name]
FROM rows
If we want to change the status of ORDER BY Clause in DESC and ASC in the over() clause
ROW_NUMBER in 2000.
There is no Row_Number in 2000. But we can get the same resullt in the below manner.
create table ROWNUMBER
([Name] VARCHAR(50),[Time] DATETIME)
GO
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
GO
SELECT [Name],[Time],(SELECT COUNT(Time)
FROM ROWNUMBER
WHERE [Name]=d.[Name] and Time >= d.Time) as Counts
FROM ROWNUMBER d
If we want to change the ORDER BY scenario in the result make chanes in <= or => in the above query.
CREATE TABLE #Temp
(SNO INT,[Name] VARCHAR(25))
---This value has entered 5 times
INSERT INTO #Temp
(SNo,[Name])
VALUES(1,'Ramesh')
GO
-----This value entered has 3 times
INSERT INTO #Temp
(SNo,[Name])
VALUES(2,'Jagan')
GO
-----------This value entered has 3 times
INSERT INTO #Temp
(SNo,[Name])
VALUES(3,'Mohan')
go
SELECT * FROM #Temp
GO
SELECT *, ROW_NUMBER()OVER(PARTITION BY SNo ORDER BY [Name]DESC)
FROM #Temp
ROW_NUMBER function will tell us how many times that particular row has been repeated. If it shows last value as 5.That means that particular row is repeated 4 times.
If we want to give sequential number to values we can make small change in the Query.
Eg:
CREATE TABLE rows
([Name] VARCHAR(20))
GO
INSERT INTO rows
([Name])
VALUES('Mahesh')
GO
INSERT INTO rows
([Name])
VALUES('Mahesh')
GO
INSERT INTO rows
([Name])
VALUES('Mahesh')
go
SELECT ROW_NUMBER()OVER(ORDER BY [Name])AS Sno,[Name]
FROM rows
If we want to change the status of ORDER BY Clause in DESC and ASC in the over() clause
ROW_NUMBER in 2000.
There is no Row_Number in 2000. But we can get the same resullt in the below manner.
create table ROWNUMBER
([Name] VARCHAR(50),[Time] DATETIME)
GO
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Ramesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
INSERT ROWNUMBER
values('Sateesh',GETDATE())
GO
SELECT [Name],[Time],(SELECT COUNT(Time)
FROM ROWNUMBER
WHERE [Name]=d.[Name] and Time >= d.Time) as Counts
FROM ROWNUMBER d
If we want to change the ORDER BY scenario in the result make chanes in <= or => in the above query.
Comments