Find Nth highest salary using RANKING functions

Suppose you want to find nth highest salary from a table,Then you can use DENSE_RANK() function.Here I am showing some example for achieving the same.           

DENSE_RANK(),RANK(),ROW_NUMBER(),NTILE() in SQL Server:

CREATE TABLE [dbo].[Person1](

     [id] [int] IDENTITY(1,1) NOT NULL,

     [name] [varchar](20) NULL,

     [sal] [int] NULL

) ON [PRIMARY]

Insert Data into table

insert  into Person1  (name,sal)values('Anil','100000')

insert  into Person1  (name,sal)values('Patel','100000')

insert  into Person1  (name,sal)values('Chikoti','300000')

insert  into Person1  (name,sal)values('Nikol','100000')

insert  into Person1  (name,sal)values('Rajeev','100000')

insert  into Person1  (name,sal)values('Binod','300000')

insert  into Person1  (name,sal)values('Pradeep','500000')

insert  into Person1  (name,sal)values('Dinel','300000')

insert  into Person1  (name,sal)values('Gemil','400000')

Brief about These: check MSDN

·         ROW_NUMBER():Returns rownumber of each row of result set.

Syntax:

ROW_NUMBER ( )

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

·        DENSE_RANK(): Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

Syntax:

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

·         RANK(): Returns the rank of each row within the partition of a result set.

Syntax:

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Now see result

 select

id,

name,

sal,

DENSE_RANK() over (order by sal) as [DENSE_RANK],

RANK() over (order by sal) as [RANK()],

ROW_NUMBER() over (order by sal) as [ROW_NUMBER()]

  from Person1

      OUTPUT:

Now if we want to find name that gets 2nd highest salary then execute this query

DENSE_RANK() can be used for this purpose because it provide a number for same values.

SELECT

a.Name,a.sal

FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM Person1) as a where rk=2

Note: you can use over(ORDER BY sal desc) or over(ORDER BY sal asc) for highest and lowest salary.

If you want to 3rd highest then put rk=3 and as you wish.

SELECT

a.Name,a.sal

FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM Person1) as a where rk=3

Partitioning Table in SQL Server:

Partitioning Table in SQL Server:

 When your database has large tables. Then partitioning will be beneficial in terms of performance, scalability and maintainability. Sql Server partitioning allows to spread data on various physical disks leveraging the concurrent performance of those disks to optimize query performance. 

To use partitioning we create a database with multiple file groups. Here is an example with three file groups. The first is Primary Data file group. Others are Secondary file group. Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive.

Create database

CREATE DATABASE partioningDB

ON PRIMARY

(

NAME='PRIMARY',

FILENAME=

'C:\FG1\fg1.mdf',--Priamry data file

SIZE=6,

MAXSIZE=600,

FILEGROWTH=1

),

FILEGROUP fg2

(NAME = 'fg2',

FILENAME =

'D:\fg2\fg2.ndf',--ndf file is secondary data files make up all the data files, other than the primary data file

SIZE = 2,

MAXSIZE=100,

FILEGROWTH=1 )

,

FILEGROUP fg3

(NAME = 'fg3',

FILENAME =

'D:\fg3\fg3.ndf',--ndf file is secondary data files make up all the data files, other than the primary data file

SIZE = 2,

MAXSIZE=100,

FILEGROWTH=1 );

GO

Create Partition Function

Now we need to create a partition range function with following syntax.

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] )

[ ; ]

Ex.

CREATE PARTITION FUNCTION func_partition (int)

 AS RANGE RIGHT

 FOR VALUES (10,50)

FOR VALUES (10,50) defines Boundaries. These boundaries define three partitions. The first contains all values less than 10. The second contains values between 10 and 49. Third contains rest of values.

Now create a partition scheme

You need to create a scheme that defines where you want to partition data. Syntax to create scheme is

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

[ ; ]

Ex.

CREATE PARTITION SCHEME scheme_partition

 AS PARTITION func_partition

 TO ([PRIMARY], fg2, fg3)

Partition is created on primary and fg2, and fg3 file groups.

Now to partitioning a table you need to create a table or alter table

Table Creation with partition

CREATE TABLE Person (fname nvarchar(40), lname nvarchar(40), [uid] int)

 ON scheme_partition ([uid])

 Partitioning on Existing Table

ALTER TABLE Person ADD CONSTRAINT [PK_uid] PRIMARY KEY CLUSTERED

(

[uid] ASC

)

WITH

(     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

)

      ON scheme_partition([uid])

GO

Now partitioning of table has been done. To test insert some data in table

insert into Person values ('A','a',1)

insert into Person values ('B','b',2)

insert into Person values ('C','c',3)

insert into Person values ('D','d',23)

insert into Person values ('E','e',24)

insert into Person values ('F','f',25)

insert into Person values ('G','g',10)

insert into Person values ('H','h',60)

insert into Person values ('I','i',58)

insert into Person values ('J','j',54)

insert into Person values ('K','k',55)

Now to verify your data run this query

SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Person';

Result:

See first partition have 3 rows means UID<10. As (1, 2, and 3)

Then Second contains 4 rows UID<50. (23, 24, 25, 10)

Then Third contains 4 rows UID>=50 as (55, 54, 58, 60)

Fetch Data from Table

Select *from Person

Sending Querying Data on Mail as Attached File

Sending Querying Data on Mail:

Sometimes we need to send data fetched from query on mail automatically. You can 

create report and after running reports you can send mail data fetched from report.

Alternative is send query data on mail and schedule this job. Now you don’t need to 

run reports and mail.

You can follow some basic steps to achieve this

·        Configure database mail

·        Write your query as here for example

  SELECT

     customerid as Customer_ID,[name] as Name,

      COUNT(name) as No_of_Login,MAX(date1) as Last_Login_Date

  FROM [db_omx_customer].[dbo].[tblIP] group by customerid,name

  order by No_of_Login,name desc

Results like:

·        Use msdb.dbo.sp_send_dbmail stored procedure to send mail

·        When you configure database mail you were provide a profile name that you need to pass with msdb.dbo.sp_send_dbmail.

As

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'mailtest',

    @recipients = sample@sample.com',

    @body_format = 'HTML',

    @query = 'SELECT

     customerid as Customer_ID,[name] as Name,

      COUNT(name) as No_of_Login,MAX(date1) as Last_Login_Date

  FROM [db_omx_customer].[dbo].[tblIP] group by customerid,name

  order by No_of_Login,name desc' ,

    @subject = 'Customer Login Details',

    @attach_query_result_as_file = 1;

When you run above query result will be sent on sample@same.com as attached text 

file containing fetched data by query.

To send data on mail automatically now you need to schedule a job  that will run at 

particular time and send data to mail id.

·        To schedule a job make it as Stored procedure for easiness as

CREATE PROC [dbo].[usp_sendadataonmail]

as

begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'mailtest', -- profile name 

    @recipients = 'sample@sample.com', -- recipients mailid 

    @body_format = 'HTML',

    @query = 'SELECT

     customerid as Customer_ID,[name] as Name,

      COUNT(name) as No_of_Login,MAX(date1) as Last_Login_Date

  FROM [db_omx_customer].[dbo].[tblIP] group by customerid,name

  order by No_of_Login,name desc' ,

    @subject = 'Customer Login Details',

    @attach_query_result_as_file = 1; -- result sent in attched file. 

END

·        Now create a job with query

 Exec sampledatabase.dbo.usp_sendadataonmail

Ref: Creation and Scheduling Job

Now data with attched file automatically will be send to mail provided by you

automatically at scheduled time.