SQL SERVER – Find Nth /Second/Third Highest Salary – Common interview Question

This is a common question in Interview “find the second highest salary of Employee details table”. I hope most of my friends also have faced this question. Here I am explaining about two different methods-

Using Row_number()
Without using any SQL Function.

This is my Employeedetails table.

Method 1: Using Row_number()

Here I am showing third highest salary.

Method 2 – With no SQL Server Function

Another method: Suggested by Fazal – one of the regular blog reader of SQLINFO.


SELECT MAX(salary)

FROM   employeedetails

WHERE  salary NOT IN (SELECT TOP 1 salary

                      FROM   employeedetails

                      ORDER  BY salary DESC) 

SQL SERVER- How to find mismatch of columns in two tables in two different databases.

When I opened my mail today, I got an email from Rajkumar on Aug 13 2011 and the content is,

Hi Varun,

Good Evening…

I am Rajkumar.D from chennai     when i searching for some query to find the mismatch in table i found your blog and found the solution from your blog and solved the issue thanks for that. Now i have two questions in sql .

1.How to find columns mismatch or columns missed in a table.

2.How to compare columns mismatch between two tables.

awaiting for your reply.

With regards,

Rajkumar.D

MILES TO GO……….

Since I am busy with my weekend, I was able to open my email only today.

Here I am explaining with  two Databases TestDb1 and TestDb2. In each DB, there is a table Employeedetails in   which TestDB2.Employeedetails is missing a column Department from TestDB1.Employeedetails.

/****** Object: Table [dbo].[EmployeeDetails] Script Date: 08/16/2011 08:35:21 ******/

CREATE TABLE [dbo].[EmployeeDetails]

(

[ID] [INT] NOT NULL,

[EmpName] [VARCHAR](50) NULL,

[Department] [VARCHAR](50) NULL,

CONSTRAINT [PK_EmployeeDetails] 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

USE [TestDB2]

GO

/****** Object: Table [dbo].[EmployeeDetails] Script Date: 08/16/2011 08:36:16 ******/

CREATE TABLE [dbo].[EmployeeDetails]

(

[ID] [INT] NOT NULL,

[EmpName] [VARCHAR](50) NULL,

CONSTRAINT [PK_EmployeeDetails] 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

Here is the script to identify the mismatch of the columns between two tables.


USE testdb1



GO



--1.How to find columns mismatch or columns missed in a table.

--2.How to compare columns mismatch between two tables.

SELECT db1.table_name,

       db1.column_name,

       ‘Missing in TestDb2…!’ AS details

FROM   testdb1.information_schema.columns AS db1

       LEFT JOIN testdb2.information_schema.columns AS db2

         ON db1.table_name = db2.table_name

            AND db1.column_name = db2.column_name

WHERE  db2.column_name IS NULL 

Modify the script as per the need.