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

Understanding RELIGION

What you understand by "RELIGION" / धर्म (pronounced as dharma)  in Hindi ?

** Request to readers: Do not read this post until you put your religious ego down, have patience and in state of accepting truth based on facts. However you are free to put your views or facts or discuss."

Keeping popular beliefs aside, the correct (correct because if I bet you 
wouldn't be able to find any wrong in it) definition of Religion lies 
as here below:

"Religion is a organized set of rules to live life in a disciplined, safe, 
pleasant, prosperous, happy, beneficial (to other living beings) manner."

" धर्मं नियमों का एक ऐसा संग्रह है जो प्राणियों को व्यवस्थित, सुरक्षित, सुखद, समृद्ध, 
खुशहाल एवं अन्य प्राणियों के लिए लाभकारी; जीवन यापन करने का सन्देश देता हैं । "


People always argue on the name of so called religions. They say that one belongs to Islam, other to Christianity, other to Buddhism and so on. Please give a thought to this that : Are really these all religions?

My answer is "NO". These all are not religions but faiths. They had been followed from centuries. But they can't be termed as religion. Religion never preach people to follow any particular God, neither it asks you to follow any acts of stupidity nor it asks you kill or eradicate non-believers.

Well, well I am not at all abusing or insulting any faith like Islam, Christianity, Buddhism, Jainism, and even Scientology. This is rather to differentiate the general belief and understanding of the term "religion" which always had been misinterpreted.

"Hinduism": When you search this term on internet, you may find it as "oldest living religion". However, no doubt it is oldest but is NOT a religion again. In fact, the term "Hinduism" doesn't stand for any faith even. However the present followers are just following crude form of pure religion. The origin of the term "Hinduism" came when Bhartiya/Indian invasion took place.

As all know, the earliest civilization took place near river "Indus". The people who inhabited near Indus river were termed as Indus and later "Hindus by invaders". In Sanskrit, "Indus" is pronounced as Sindhu  and thus gave birth to the term Hindu  or "Hindu".

Now the question arise that "What should be taken as pure religion?".
I had talked about "crude form of pure religion". Then what is pure form of religion.

"Vedas" or वेद (in hindi): These are the oldest religious texts of unknown origins. I said "religious texts" because they doesn't tell you to follow any proper God, or person, or asks you to eradicate non-believers or any thing like that. They just tell you to how to live a proper life which had been gifted to you from the creator of universe. The term "Ved" itself means "knowledge".

Vedas nowhere asks you to pray for any particular god such as Lord Krishna, Lord Vishnu, Lord Christ, Allah etc. but tells that "There is a creator (no doubt there must be a creator of anything) of the whole universe which we can term as God. We should thank him for creation and giving brains to understand the creation." In four Vedas which are dedicated to four different subjects of life, instructs you to follow rules to live the life as described in the definition of religion above (in the post).

The four Vedas are:

Rigya Ved (ऋग्वेद )
Yajur Ved (यजुर वेद )
Sam Ved (साम वेद )
Atharva Ved (अथर्व वेद )

Vedas (वेद ) classified human beings as:
 Arya meaning "noble". One who follows religion (NOT Hinduism, Jainism, Christianity, Islam etc.)
Anarya meaning "not noble". One who doesn't follow religion.

Arya are the humans who live a disciplined life by following the rules as stated in religion. A Christian can be an Arya so as a Muslim can be Arya. The definition is based on deeds (karma) and living style and not on following of a particular faith. Question yourself, from where Christianity, Islam, Buddhism etc. originated. Buddhism was started by Lord Buddha (who was earlier following so called Hinduism). He differed some facts on Vedas and started a new faith called as Buddhism. Jainism has similar origins. Islam were the origin of Mohammed. I counter this fact as earlier reference are also available. but if Mohammed was the starter then it becomes a faith again. Also, many preaching defer from the definition of religion such as termination of non-believers.

There is so much to write but possibly I am not able to decide where to start with. Questions such as: "Who are Lord Krishna, Lord Rama, Lord Christ, Mohammed, Budhha, Gurunanak etc.? Why only I have took Vedas as defining pure religion? What the present faith/rituals of Hindus meant for? What are rituals in different faiths? and lots more.........

At last, I would like to apologize if I had hurt feelings of anybody. But my aim is to follow truth. Yes much had been left to write and state but for the time being its enough. I would soon come up with more facts, posts which may light up the minds and keep truth alive.