Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This blog incorporates articles about ASP.Net 3.5 and 4.0. Also good amount of concentration is laid on .net projects,book reviews,C# articles,jobs in present industry and some general topics.
Browse by Tags · View All
ARTICLE 61
SQL Server 31
TSQL 30
C# 16
ASP.Net 11
JumpStart 8
GENERAL 8
ASP.Net MVC 7
C# 4.0 6
ASP.Net 4.5 5

Archive · View All
December 2012 22
July 2012 12
May 2012 8
January 2013 7
April 2013 4
March 2013 4
February 2013 4
January 2012 4
August 2012 2
May 2013 1

Rami Vemula's Blog

RANKING using OVER Clause in TSQL

Dec 28 2012 12:00AM by rami   

There are majorly 4 different ranking functions in SQL Server. They are –

  1. ROW_NUMBER()
  2. RANK()
  3. DENSE_RANK()
  4. NTILE(INT)

They all are different and in my own terminology they can be used to define row versions in programmatic point of view. I mean to say, when we want more precise control on every row of a group or partition, these functions will give us more flexibility. Let us first understand these functions –

ROW_NUMBER()
Returns the incremental number of a row within a partition of a result set, starting at 1 for the first row in each partition. When no partition is specified it treats all rows as single partition and gives each row different row number starting from 1.

RANK()
Returns the rank of each row within the partition of a result set. when partition was specified all the rows in a partition of same Order by clause would be ranked with the same number and next order by group will be getting rank from (Rank of previous Order by + number of rows in the previous Order by). When no partition specified, RANK() does take ORDER BY column and associates ranks using the same.

DENSE_RANK()
Returns the rank of rows within the partition of a result set, but unlike RANK() function, it returns continuous ranks for partitions without any exclusions. When no partition has been specified, then DENSE_RANK() takes the ORDER BY clause and
then associates continuous ranking to the rows. When partition by is specified it groups the rows by partition clause and then gives continuous ranks to the rows based on Order by clause, which is going to be continuous.

NTILE(INT)
Distributes the rows in an ordered partition into a specified number of groups. From MSDN, If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If
on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

 

Based on above understanding lets see some sample code –

/**************************** Create TestDB Database *****************************/ 

IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO

USE [TestDB]
GO

/**************************** Create Employee Table ******************************/

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'EmployeeRoles')
BEGIN
    DROP TABLE [dbo].[EmployeeRoles];
END
GO

CREATE TABLE [dbo].[EmployeeRoles](
    [EmpRoleId] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [EmpRole] [nvarchar](32) NOT NULL,
    [InsertedDate] [datetime] NOT NULL DEFAULT GETDATE()
)
GO

/************************ Enter Employee Roles Details *************************/

INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (1,'CEO');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (2,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (3,'Sdet');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (4,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (5,'Test');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (6,'Sdet');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (7,'CTO');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (8,'PM');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (9,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (10,'Sdet');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (11,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (12,'Test');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (13,'PM');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (14,'Test');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (15,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (16,'Test');
GO

/*********************** ROW_NUMBER() with PARTITION *************************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            ROW_NUMBER()
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpId] ASC) AS RowNumberWithPartition
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[RowNumberWithPartition]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/********************** ROW_NUMBER() without PARTITION ***********************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            ROW_NUMBER()
                OVER(ORDER BY [EmpId] ASC) AS RowNumberWithOutPartition
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[RowNumberWithoutPartition]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/*************************** RANK() with PARTITION ***************************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            RANK()
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpId] ASC) AS RankNumberWithPartition
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[RankNumberWithPartition]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/************************* RANK() without PARTITION **************************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            RANK()
                OVER(ORDER BY [EmpId] ASC) AS RankNumberWithoutPartition
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[RankNumberWithoutPartition]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/*********************** DENSE_RANK() with PARTITION *************************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            DENSE_RANK()
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpId] ASC) AS DenseRankWithPartition
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[DenseRankWithPartition]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/********************** DENSE_RANK() without PARTITION ***********************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            DENSE_RANK()
                OVER(ORDER BY [EmpRole] ASC) AS DenseRankWithoutPartition
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[DenseRankWithoutPartition]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/************************ NTILE(int) with PARTITION **************************
******************************************************************************/

WITH GetRowRanks AS
(
     SELECT
            [EmpId],
            [EmpRole],
            NTILE(4)
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpRole] ASC) AS NtileNumber
      FROM
            [dbo].[EmployeeRoles]
)
SELECT
       GRR.[EmpId],
       GRR.[EmpRole],
       GRR.[NtileNumber]
FROM
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO

/**************************** Drop TestDB Database *****************************/

USE master
GO

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

 

OUTPUT –

 

EmpId       EmpRole                          RowNumberWithPartition
----------- -------------------------------- ----------------------
1           CEO                              1
7           CTO                              1
2           Dev                              1
4           Dev                              2
9           Dev                              3
11          Dev                              4
15          Dev                              5
8           PM                               1
13          PM                               2
3           Sdet                             1
6           Sdet                             2
10          Sdet                             3
5           Test                             1
12          Test                             2
14          Test                             3
16          Test                             4

(16 row(s) affected)

EmpId       EmpRole                          RowNumberWithoutPartition
----------- -------------------------------- -------------------------
1           CEO                              1
7           CTO                              7
9           Dev                              9
2           Dev                              2
4           Dev                              4
11          Dev                              11
15          Dev                              15
13          PM                               13
8           PM                               8
10          Sdet                             10
3           Sdet                             3
6           Sdet                             6
16          Test                             16
14          Test                             14
12          Test                             12
5           Test                             5

(16 row(s) affected)

EmpId       EmpRole                          RankNumberWithPartition
----------- -------------------------------- -----------------------
1           CEO                              1
7           CTO                              1
2           Dev                              1
4           Dev                              2
9           Dev                              3
11          Dev                              4
15          Dev                              5
8           PM                               1
13          PM                               2
3           Sdet                             1
6           Sdet                             2
10          Sdet                             3
5           Test                             1
12          Test                             2
14          Test                             3
16          Test                             4

(16 row(s) affected)

EmpId       EmpRole                          RankNumberWithoutPartition
----------- -------------------------------- --------------------------
1           CEO                              1
7           CTO                              7
9           Dev                              9
2           Dev                              2
4           Dev                              4
11          Dev                              11
15          Dev                              15
13          PM                               13
8           PM                               8
10          Sdet                             10
3           Sdet                             3
6           Sdet                             6
16          Test                             16
14          Test                             14
12          Test                             12
5           Test                             5

(16 row(s) affected)

EmpId       EmpRole                          DenseRankWithPartition
----------- -------------------------------- ----------------------
1           CEO                              1
7           CTO                              1
2           Dev                              1
4           Dev                              2
9           Dev                              3
11          Dev                              4
15          Dev                              5
8           PM                               1
13          PM                               2
3           Sdet                             1
6           Sdet                             2
10          Sdet                             3
5           Test                             1
12          Test                             2
14          Test                             3
16          Test                             4

(16 row(s) affected)

EmpId       EmpRole                          DenseRankWithoutPartition
----------- -------------------------------- -------------------------
1           CEO                              1
7           CTO                              2
9           Dev                              3
2           Dev                              3
4           Dev                              3
11          Dev                              3
15          Dev                              3
13          PM                               4
8           PM                               4
10          Sdet                             5
3           Sdet                             5
6           Sdet                             5
16          Test                             6
14          Test                             6
12          Test                             6
5           Test                             6

(16 row(s) affected)

EmpId       EmpRole                          NtileNumber
----------- -------------------------------- --------------------
1           CEO                              1
7           CTO                              1
9           Dev                              1
2           Dev                              1
4           Dev                              2
11          Dev                              3
15          Dev                              4
13          PM                               1
8           PM                               2
10          Sdet                             1
3           Sdet                             2
6           Sdet                             3
16          Test                             1
14          Test                             2
12          Test                             3
5           Test                             4

(16 row(s) affected)


Republished from Rami Vemula [15 clicks].  Read the original version here [2 clicks].

rami
538 · 0% · 70
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"RANKING using OVER Clause in TSQL" rated 5 out of 5 by 1 readers
RANKING using OVER Clause in TSQL , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]