Optimized query to find third and nth highest value or salary from given table in SQL

Table name : demotable 

select * from demotable

3rd maximum value:

SELECT TOP 1 value FROM (
   SELECT DISTINCT TOP 3 value
   FROM demotable
   ORDER BY value DESC) AS val
ORDER BY value ASC

OR 

SELECT MAX(value)
FROM demotable
WHERE value IN(SELECT DISTINCT TOP 3 value FROM demotable ORDER BY value ASC)

 

To get Nth highest value from above table

Nth maximum salary:

SELECT MAX(value)
FROM demotable
WHERE value IN(SELECT TOP N value FROM demotable ORDER BY valueASC)

The SQL Server Service Broker for the Current Database is not Enabled, and as a Result Query Notifications are not Supported. Please Enable the Service Broker for this Database

Start SQL Server and write this query to enable broker.

ALTER DATABASE Demo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
To check weather broker is enable or not run this query.

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘Demo’

To kill all the database running process, write this query.

USE master

go

DECLARE @dbname sysname

SET @dbname = ‘Demo’

DECLARE @spid int

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)

WHILE @spid IS NOT NULL

BEGIN

EXECUTE (‘KILL ‘ + @spid)

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid

END

ALTER DATABASE @dbname SET ENABLE_BROKER

NoteDemo is my database name.

SQL JOINS

DESCRIPTION

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of SQL joins:

  • SQL INNER JOIN
  • SQL LEFT OUTER JOIN
  • SQL RIGHT OUTER JOIN
  • SQL FULL OUTER JOIN

Inner Join

Consider there are two tables as following

a) Student Table                                                           b) Advisor Table

student                            advisor

 

Now let us join these tables using INNER JOIN .

SQL>    SELECT * FROM Student S  

INNER JOIN Advisor A

ON

S.Advisor_Id=A.Advisor_Id

The result would be as follows.

innerjoin