Rank RowNumber And Dense_Rank Working Explained

-
USE [tempdb]

GO

Drop TABLE [dbo].[Table_2]

GO

/****** Object:  Table [dbo].[Table_2]    Script Date: 02/06/16 17:51:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table_2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Player] [varchar](50) NULL,
[Score] [numeric](18, 3) NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



GO

INSERT INTO [dbo].[Table_2]
           ([Player]
           ,[Score])
     VALUES
           ('A',9.9),
  ('B',9.8),
  ('C',9.7),
  ('D',9.6),
  ('E',9.6),
  ('F',9.2)
GO


Select ID,Player,Score,Row_Number() Over (order by Score) as RowNumber,Rank() Over (order by Score) as Rnk,Dense_Rank() Over (order by Score) as DenseRank from table_2


-- RowNumber : Will not Skip number
-- Rank : When Found Same value Value or Ties it will have same number for both and will Skip the Number
-- Dense_Rank : when Found Same Value Or Ties it will have numer for both but next number will not Skip

Comments

Post a Comment

Popular posts from this blog

Rebuild Index

-
DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO
Read more

Exclude property of Class Object from Serialization

-
Exclude property of Class Object from Serialization   Json.NET has the ability to conditionally serialize properties by placing a ShouldSerialize method on a class. This functionality is similar to the XmlSerializer ShouldSerialize feature.     ShouldSerialize To conditionally serialize a property, add a method that returns boolean with the same name as the property and then prefix the method name with ShouldSerialize. The result of the method determines whether the property is serialized. If the method returns true then the property will be serialized, if it returns false then the property will be skipped.     Live Code We need to exclude hsn and Doc_issue when they are nothing in runtime (Same is done Nothing when not to be Serialized)   Public Structure Json_Parent         Public Property gstin As String         Public Property fp As String           Public P...
Read more

Multiple Connection from Config File

-
Config File Change     AppDB_1 ;Data Source= MyPC\Sql2008 " />     AppDB_2 " connectionString="Password= 123456 ;Persist Security Info=False;User ID=sa;Initial Catalog= AppDB_2 ;Data Source=MyPC\Sql2008" />    Front End Change Dim strConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings(" AppDB_1 ").ToString cnAppDB_1 = New SqlClient.SqlConnection(strConnection) cnAppDB_1 .Open() System.Configuration.ConfigurationManager.ConnectionStrings(" AppDB_2 ").ToString cnAppDB_2  = New SqlClient.SqlConnection(strConnection) cnAppDB_2 .Open()
Read more