Finding nth highest values... Script in T-SQL

The following script will gives you the nth highest values.

It will find nth highest salary from employee table

which has empid,salary fields.

Set value of @nthhighest variable to whatever number you want

to assign. Result will be according to the specified value.

declare @nthhighest int

set @nthhighest = 5

select * from employee order by salary desc

select top 1 salary from 

(

select top (@nthhighest) salary from employee

order by salary desc

) as employee

order by salary 

SELECT max(salary) FROM employee WHERE salary NOT IN 

(

SELECT top (@nthhighest - 1)  salary FROM employee order by salary desc

)

Delete duplicates rows of table when there is no any unique column exist Script in T-SQL

Here when there are many duplicates row in table and

table do not have any unique columns

example.

suppose one table 'test' have three column.

1.tid 

2.tname 

3.tcity

and this table contain records like.

tid tname tcity

1 A G

1 A G

1 A G

1 A G

2 B B

2 B B

3 C V

3 C V

3 C V

3 C V

3 C V

Here when group by query fire like.

select tid,tname,tcity

from test

group by tid,tname,tcity

output of above query is.

tid tname tcity

1 A G

2 B B

3 C V

So here total three records exists

So when u want to delete duplicats records

from this type of table, i show i idea for it

like..

create table test

(tid int not null,

tname varchar(50) not null,

tcity varchar(50) not null

)

go

go

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(2,'B','B')

insert test values(2,'B','B')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

go

Here i show u one query which delete duplicats rows

where there is not any unique key..

 

 

delete   maindata

from 

(

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as maindata

where maindata.rowid not in

(

select

rowid

from (

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as datawithRow

where rowid in 

(

select

top 1 rowid

from (

select ROW_NUMBER() OVER (ORDER BY  tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as idatawithRow

where

idatawithRow.tid = datawithRow.tid and

idatawithRow.tname = datawithRow.tname and

idatawithRow.tcity = datawithRow.tcity

) and

datawithRow.tid = maindata.tid and

datawithRow.tname = maindata.tname and

datawithRow.tcity = maindata.tcity

)

----------------------------------------------------------------------------------------------

After executing delete query

select tid,tname,tcity

from test

group by tid,tname,tcity

Output will be...

tid tname tcity

1 A G

2 B B

3 C V