SQL SERVER - How To find Nth Highest Salary of Employee

A Quite common Question in any of the forum - “How To find Nth Highest Salary of Employee”

For this, Let us first create a table Employee

Create Table Employee
(Empid int NOT NULL identity(1,1) Primary key,
Empname varchar(50),
DeptId int,
Salary money)

Insert into Employee
Select 'AAA',1,30000
Union ALL
Select 'BBB',1,45000
Union ALL
Select 'CCC',1,23000
Union ALL
Select 'DDD',1,30000
Union ALL
Select 'EEE',1,20000
Union ALL
Select 'FFF',1,70000
Union ALL
Select 'GGG',1,39000
Union ALL
Select 'HHH',1,130000
Union ALL
Select 'III',1,35000
Union ALL
Select 'JJJ',1,43000                                                        Union ALL
Select 'KKK',1,23000
Union ALL
Select 'LLL',1,80000
Union ALL
Select 'MMM',1,35000
Union ALL
Select 'NNN',1,65000

Now we will retrieve the details from the Employee table in the increasing order of Salary to easily cross check the results of the coming queries.

Select * From Employee order by Salary 

The Output is :

Now, If we want to get the first highest salary from the Employee table, we can easily write the query as below

Select Max(Salary) Max_salary From Employee

Well, Now we want to get the second highest salary  from the Employee table, this can also be achieved with little bit effort.

Select Max(Salary) as Second_max From Employee  A Where  Salary < (Select Max(Salary) From Employee)

Or

Select Top 1 Salary as Second_max From (
Select Distinct Top 2 Salary
From Employee Order by Salary Desc) Highest_Salaries
Order by Salary

Or

Select Distinct Salary as Second_max From Employee A Where 
2 =(Select Count(Distinct Salary) From Employee B Where
A.Salary <=B.Salary)

The out put is :

Similarly to get the 3rd, 4th,5th,6th,….nth Highest salary from the Employee table, we can replace 2 in the above query with 3,4,5…

The general query will be like this.

Select Top 1 Salary as Second_max From (
Select Distinct Top N Salary
From Employee Order by Salary Desc) Highest_Salaries
Order by Salary

Or

Select Distinct Salary as Second_max From Employee A Where  
N =(Select Count(Distinct Salary) From Employee B Where
A.Salary <=B.Salary)

where N=1,2,3,…..

 

SQL SERVER - How To List all Specified Week Days Between two given Dates

There will be some situations, where we need to list all Specified Weekdays between two given dates. Let us suppose, we need to list all the Sundays between two given dates. This Can be achieved by using the Common Table expression(CTE).

Suppose Our requirement is to pick all Sundays between 1st Jan 2009 and 30th Jan 2009.

For this, We can try like this :

Declare @StartDate Datetime, @EndDate Datetime

Set @StartDate = '01/01/2009'
Set @EndDate = '01/30/2009';

WITH WeekDay(FirstDay,NextDay) AS
(SELECT @StartDate AS StartDate,                       DATEADD(d,1,@StartDate) AS NextDay
UNION ALL
SELECT NextDay AS FirstDay,                                   DATEADD(D,1,NextDay) AS NextDay FROM WeekDay
WHERE NextDay BETWEEN @StartDate AND @EndDate)

SELECT FirstDay 'Sundays' FROM WeekDay WHERE   DATENAME(DW,FirstDay) = 'Sunday'

First Of all, we have declared @StartDate and @EndDate as Datetime Data type variables

Now We have used the Common Table Expression to retrieve all the days in between the @StartDate and @EndDate.

In the Next step, we retrieved all Sundays from this Common Table Expression using the DATENAME() function.

If we want to place this in a procedure, then it will look like this as below:

Create Proc List_All_Sundays(@startdate datetime,@enddate datetime) AS

Begin

WITH WeekDays(FirstDay,NextDay) AS                     (SELECT @StartDate AS StartDate,DATEADD(d,1,@StartDate) AS NextDay                                   UNION ALL                                                             SELECT NextDay AS FirstDay, DATEADD(D,1,NextDay) AS NextDay FROM WeekDays                                       WHERE NextDay BETWEEN @StartDate AND @EndDate)

SELECT FirstDay 'Sundays' FROM WeekDays WHERE DATENAME(DW,FirstDay) = 'Sunday'

End

Now Let us execute this Procedure and check the result

Exec List_All_Sundays '01/01/2009','01/31/2009'

The Output is :

SQL SERVER - How To Delete Duplicate Records From a Table

Here is the process to delete the duplicate records from a table

Let us first create a table called Product.

Create Table Product (ProdId int, ProdDesc varchar(25))

Insert Into Product
Select 1,'Adjustable Race'
Union All
Select 2,'Bearing Ball'
Union All
Select 1,'Adjustable Race'
Union All
Select 3,'Chainring Bolts'
Union All
Select 4,'Down Tube'
Union All
Select 4,'Road End Caps'
Union All
Select 1,'Adjustable Race'
Union All
Select 6,'Chainring'
Union All
Select 1,'Adjustable Race'
Union All
Select 7,'Headset Ball Bearings'
Union All
Select 8,'Blade'
Union All
Select 3,'Chainring Bolts'
Union All
Select 9,'Chainring Nut'

Now the records in the table are as shown below :

               

Now, In Order to delete the Duplicate records from the Product table, As a first step, we will check the number of sets of duplicate Primary Key values that exist and the count of duplicates for each set.

Select the duplicate key values in to a original table Duplicate_Products.

SELECT ProdId, ProdDesc,Count(*) RecCount INTO Duplicate_Products FROM Product GROUP BY ProdId, ProdDesc
HAVING Count(*) > 1

On retrieving the records from  Duplicate_Products,     

Select * From Duplicate_Products

      

Now, Select the duplicate rows into a original table, eliminating duplicates in the process.

SELECT DISTINCT P1.* INTO Unique_Products FROM Product P1, Duplicate_Products P2 WHERE P1.ProdId = P2.ProdId AND P1.ProdDesc = P2.ProdDesc

On retrieving the records from  Unique_Products,

Select * from Unique_Products

       

At this point, the Unique_Products table should have unique Primary Keys.

Now the next step is, Deleting the duplicate rows from the original table Product.

DELETE  Product FROM Product P1,Duplicate_Products P2  WHERE P1.ProdId =  P2.ProdId AND P1.ProdDesc = P2.ProdDesc

Now Put the unique rows back in the original table Product.

INSERT Product SELECT * FROM Unique_Products

Now On retrieving the records from Product table,we can see that all the duplicate records has been deleted.

SELECT * From Product

SQL SERVER - How To Insert a New Column At a Particular Position in a Table?

I have seen the Following question more frequently in many of the forums like “How to insert a new column at a particular Position in a table? ”

In general, If we add a new column in a table, this column will be added at the end of all the existing columns. In technical aspect, There is no significance to the order of the columns in a table. But for human consumption the column order has significance.

Let us see various methods to insert a new column at a particular position in a table.

Method 1:

1. Let us Consider a table Organization table with columns OrgId, AcctNum and OrgName.

CREATE TABLE Organization (OrgId int,Acctnum varchar(10), OrgName varchar(30))

INSERT INTO Organization
Select 1,'C00001','DEF Corp'
Union All
Select 2,'C00002','GHI PVT Ltd'
Union All
Select 3,'C00003','XYZ Corp'

Now the  output of the table is :

Select * From Organization

Result :

2. Now we want to add a new Column CompanyId between Acctnum and OrgName.

For this, we will Create a new Table Organization_New  with the desired column order.

CREATE TABLE  Organization_New (OrgId int, Acctnum varchar(10), CompId Int Null, OrgName varchar(30))

3. Now Insert values from the existing Old table  Organization to the New Table ‘Organization_New’

Insert Into Organization_New(OrgId,Acctnum,OrgName) Select OrgId,Acctnum,OrgName From Organization

Here the Default values for the new column CompanyId will be Null.

4. Now Drop the old table Organization and Rename 'Organization_New' to 'Organization'.

----Now Drop the old table
Drop Table Organization

---Rename the New table SP_Rename 'Organization_New' , 'Organization'

5. Now you can update the values of the new column CompId using the Update statements.

Update Organization set Compid= 34567 Where Orgid=1
Update Organization set Compid= 34568 Where Orgid=2
Update Organization set Compid= 34569 Where Orgid=3

Finally here is the Organization table with desired Column order. Now If we want to see the table details,

Select * From Organization

Result :

Method 2:

The second method is to add the new column ‘CompId’ at the desired position in the Organization table in the Object Explorer of the SQL Server Management Studio.

For this First select the table in the in the Object Explorer of the SQL Server Management Studio. Right Click on the Table Organization and Select ‘Modify’

    A Table Designer Window will be displayed at the right hand side of the SQL Server Management Studio as shown below.

Now in Order to add a new column CompId between AcctNum and OrgName, Right click on the Column OrgName and you will be seen a Popup window. Now Select the ‘Insert Column’  from the Popup window and Add the new Column ‘CompId’ between AcctNum and OrgName.

Now the Table Designer will be shown as below :

 

Now Save the Changes and click the Refresh button and check the  columns of the table. Now we can see the newly added column CompId under the columns list.

SQL SERVER - How To Count the Comma Separated Values

In Order to Count the number of individual values in a comma separated string we can use the Following simple method.

Let us see this through an example.

Let us create a new table Student_attendance that maintains the student id and their attendance details in a particular week as shown in the below format:

Create Table Student_attendance(StudId Int, DaysPresent Varchar(20))

Insert Into Student_attendance
Select   1,   '1,2,3,4'
Union All
Select   2,   '2,3,4'
Union All
Select   3,   '1,3,4,5,6'
Union All
Select   4,   '1,2,3,5,6,7'
Union All
Select   5,    '1,2,6'

Now, Let us see the Output of the Select Statement.

Select * From Student_attendance   

  

Now to count the number of days a student has attended to the class, we can count the number of occurrences of ',' and add 1 to the count.

This can be achieved by using the below query :

Select StudId,LEN(DaysPresent) - LEN(REPLACE(DaysPresent, ',', ''))+1 No_Of_Days_Present
From Student_attendance

This results :

 

SQL SERVER - How To Use ‘Order By’ Clause In a View

In general, we cannot use 'Order By' clause in a view Definition. If we try to use the 'Order By' clause in a view definition, it will give the following error message

'The ORDER BY clause is invalid in views, inline functions, derived tables, sub queries, and common table expressions, unless TOP or FOR XML is also specified'

In order to solve this, there are two alternatives.

First     One is Using 'Top' keyword in the view definition Second One is Using the 'Order By' Clause in the View Retrieval.

Let us see the First case through an example.

Method 1 : Creating a view using DimEmployee Dimension table with Top keyword

Syntax:

Create View vw_EmpDetails AS
Select Top 100 Percent EmployeeKey,FirstName,LastName,Title,DepartmentName From dbo.DimEmployee
Order By EmployeeKey

Method 2 : Using the 'Order By' Clause directly in the view

As a first step we will create a view with out using the 'TOP' keyword.

Syntax:

Create View vw_EmpDetails AS
Select EmployeeKey,FirstName,LastName,Title,DepartmentName From dbo.DimEmployee

Now, we will use the 'Order By' clause in the Select statement.

Syntax: 

Select * From vw_EmpDetails
Order By EmployeeKey

SQL SERVER - Types Of Indexes

Index is that which can be used for uniqueness and Fast Data Retrieval

There are two types of indexes. They are :

1.Clustered index
2. Non Clustered index

Clustered index is that in which all the records are arranged in sorted order on the index key where as, in case Of Non Clustered index no records will be in sorted order.

In case of Clustered index as the data is in the Sorted Order and there will be minimal effort to retrieve the data. But in case of Non clustered index maximum error must be kept to retrieve data with Range Search.

In Clustered index, the Actual data is available at the end of the leaf node. Hence there will be only one Clustered index. In Non Clustered index the address will be stored in the leaf nodes. Hence there may be many number of Non Clustered indexes.

The number of Non Clustered indexes in a Table are 249 while the number of Clustered index is only One.