SQL Query to find nth highest value in a database table

General query to find out nth highest value (here salary)is:

SELECT *
FROM Test_Table T1
WHERE ( n ) = (
                SELECT COUNT( DISTINCT ( T2.Salary ) )
                FROM Test_Table T2
                WHERE T2.Employee_Salary >= T1.Salary
            )

Here, you may give value for "n" to calculate the nth highest value.

How it works??

This query involves use of an inner query.
Inner queries can be of two types. 

•  Correlated (where inner query runs in conjunction to outer query)
• Uncorrelated (where inner query can run independently of outer query)

Above is the example of correlated query.

When the inner query executes every time, a row from outer query is processed. Inner query return the count of distinct  salaries which are higher than the currently processing row’s salary column. Anytime, it find that salary column’s value of current row from outer query, is equal to count of higher salaries from inner query, it returns the result.

Performance Analysis:

Inner query executes every time, one row of outer query is processed, this brings a lot of performance overhead, specially if the number of rows are too big.

To avoid this, one should use DB specific keywords to get the result faster. For example in SQL server, one can use key word TOP like this:

SELECT TOP 1 Salary

FROM

(

    SELECT DISTINCT TOP N Salary

    FROM Test_Table

    ORDER BY Salary DESC

) A

ORDER BY Salary

Here the value of "N" should be greater than 1.

**Have took help of Lokesh Gupta's article while writing this post.

SQL Server Solutions : Change Case of all column headers of a database in one shot

Situations arise when you need to change the letter case of column headers in a database and you fear of regenerating whole database or renaming in a tedious manner of one by one.

However you may apply simple solutions defined below for the situation.

Solution applies to:
SQL Server 2008 R2 and earlier


(a) For Changing to All caps:

SELECT 'EXEC sp_rename ''' + object_name(C.[object_id]) + '.' + C.name +
''', ''' + convert(varchar(500), upper(substring(C.name,1,499))) + ''', ''COLUMN'';'
FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C
ON C.[object_id] = O.[object_id]
WHERE IS_MS_SHIPPED != 1 AND TYPE = 'U'

(b) For First Capital letter only:

SELECT 'EXEC sp_rename ''' + object_name(C.[object_id]) + '.' + C.name + 
''', ''' + 
convert(varchar(500), upper(substring(C.name,1,1)) + 
lower(substring(C.name,2,499))) + ''', ''COLUMN'';' 
FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C 
ON C.[object_id] = O.[object_id] 
WHERE IS_MS_SHIPPED != 1 AND TYPE = 'U'

You just mention a cursor for situation (a) and (b) as:

declare commands cursor for
/*Here you may write the select statements given above for situations (a) and (b)*/

declare @cmd varchar(max)

open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
  exec(@cmd)
  fetch next from commands into @cmd
end

close commands
deallocate commands


For giving title case you directly run function given below:

For Title Case:
CREATE function TitleCase(@initialstring varchar(500)) returns varchar(500) 
as 
begin 
declare @Reset bit; 
declare @Ret varchar(8000); 
declare @i int; 
declare @c char(1); 

select @Reset = 1, @i=1, @Ret = ''; 

while (@i <= len(@initialstring)) 
select @c= substring(@initialstring,@i,1), 
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end, 
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end, 
@i = @i +1 
RETURN @Ret 
end 
go 

grant execute on propercase to public; 

SELECT 'EXEC sp_rename ''' + object_name(C.[object_id]) + '.' + C.name + 
''', ''' + 
dbo.TitleCase(C.name) + ''', ''COLUMN'';' as 'Execute' 
FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C 
ON C.[object_id] = O.[object_id] 
WHERE IS_MS_SHIPPED != 1 AND TYPE = 'U'; 


Their is one alternate solution for Title case i.e.:

USE sampleDB
GO
/****** Object: UserDefinedFunction [dbo].[fnUNDERSCORE_SEPERATORtoCapitalizationChange ]  **/
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON
GO 
-- =============================================
-- Description: Takes a string with underscore_separator and returns a string 
with ProperCase
-- Example: select dbo.fnUNDERSCORE_SEPERATORtoProperCase('ADULT_QUAL IF_OSIDU')

-- ============================================= 
CREATE FUNCTION [dbo].[fnUNDERSCORE_SEPERATORtoProperCase] (
@underscore_seperated_string sysname  
) 
RETURNS sysname 
AS  
BEGIN  
DECLARE  
@pos int,
@retval sysname,
@letter char(1),
@upper bit  
SELECT  
@pos = 1
,@retval = ''
WHILE @pos <= LEN(@underscore_seperated_string)
BEGIN
IF ASCII(@letter) = 95
OR @pos = 1
SET @upper = 1
ELSE
SET @upper = 0
SET @letter = CASE @upper
WHEN 1
THEN UPPER(SUBSTRING(@underscore_seperated_string, @pos, 1))  
ELSE LOWER(SUBSTRING(@underscore_seperated_string, @pos, 1))
END
SELECT
@pos = @pos + 1,
@retval = @retval + @letter
END
RETURN (@retval)
END