Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Ranking Functions

Ranking Functions:

SQL server 2005+ has four ranking functions. Ranking functions return a ranking value for each row in a partition. Ranking functions are nondeterministic.

T-SQl provides the following four ranking functions:

·         RANK()

·         DENSE_RANK()

·         NTILE()

·         ROW_NUMBER()

 

RANK () function:

Returns  rank of each row within a partition of result set, starting at 1 for the first row in each partition.

      Return type for Rank() function is bigint. Partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied.

SYNTAX:

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Example:

SELECT  RANK() OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName

FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId


Note: if two or more rows tie for rank, in this case each tied row receives same rank.

 

DENSE RANK () function:

 Returns the sequential number of row or rank of each row within a partition of result set without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. Return type for Rank() function is bigint

Main difference between RANK() and DENSE_RANK() function is this will not consider any gaps in the ranking.

Syntax:

 

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Example:

SELECT  DENSE_RANK() OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName

FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId

 


Note: For tied records it will consider same rank but for next record rank is tied record rank plus one.

 

NTILE() function:

This inbuilt function Distributes rows the rows into specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

      Parameter we are passing to NTILE(integer expression) Is a positive integer constant expression that specifies the number of groups into which each partition must be divided. integer_expression can be of type int, or bigint.

Syntax:

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Example:

SELECT  NTILE(3) OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName

FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId

 

ROW_NUMBER Function:

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Return type for this function is bigint

Syntax:

ROW_NUMBER ( )

    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

 

Example:

-- Get total order amount in shop by month wise and year wise

-- ROW_NUMBER() function returns unique row number value within a partition --- of a result set

-- by OrderYear and OrderMonth

SELECT ROW_NUMBER() OVER (ORDER BY OrderSummery.OrderYear, OrderSummery.OrderMonth) AS [No.]

    , OrderSummery.OrderYear, OrderSummery.OrderMonth

    , OrderSummery.OrderAmountByMonth

FROM

(SELECT YEAR(OrderDate) OrderYear

    , MONTH(OrderDate) OrderMonth

    , SUM(Total) OrderAmountByMonth

FROM [Order]

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

) OrderSummery

 


 

Difference between UNION and UNION ALL

Difference between UNION and UNION ALL:

  1. UNION returns only distinct records removes duplicate records.

  2. UNION ALL returns all records including duplicate records.

  3. UNION has to perform distinct sort to remove duplicates, which makes it less fast than UNION ALL.

UNION (Merge Join) cost 46%, Union All

 

Union All:


 

Note:

·        While using UNION and UNION ALL operators, the number of columns must be same, and data columns have to be same and they have to in same order.

·        ORDER BY clause should be used in last SELECT statement in the UNION query.

·        Both statements should have some order, and equal number of columns.

INTERSECT Clause:

INTERSECT Clause the SQL INTERSECT clause/operator is used to combine two SELECT statements, INTERSECT returns only common rows returned by the two SELECT statements.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

INTERSECT

SELECT * FROM [Employee]


EXCEPT Clause:

EXCEPT Clause the SQL EXCEPT clause/operator is used to combine two SELECT statements EXCEPT returns only rows, which are not available in second SELECT statement.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

Except

SELECT * FROM [Employee]


 

UNION and UNION ALL explained

Union:

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements.

SQL UNION clause/operator removes all duplicate rows and returns only distinct rows.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

UNIO

SELECT * FROM [Employee]


Union All:

The UNION ALL clause/operator is used to combine the results of two or more select statements

Including duplicate rows/records.

Syntax:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

UNION ALL

SELECT * FROM Employee


 

difference between union and union all explained in :http://www.codechef4u.com/post/2015/04/06/difference-between-union-and-union-all