Previously I have written a post on Find out Nth highest value in SQL but that is not a good solution. You should try to avoid using “In” clause in your queries. The “In” clause uses lot of memory and should be avoided. So this post tells how to do the same using “Joins” rather than “In” clause.

The below query will fetch the 3rd highest salary,

select top 1 e.firstname, e.lastname, e.salary from employees e
left outer join (select top 2 employeeid from employees order by salary desc) b on e.employeeid=b.employeeid where b.employeeid is null
order by e.salary desc

If you want to fetch some other number change the “2″ in the following line in the above query

 (select top 2 employeeid from employees order by salary desc) b

If you know a better way, please let us know.
Hope this helps

This is the traditional method for inserting multiple values in a table.

USE YourDBName
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘First’,1);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Second’,2);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Third’,3);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Fourth’,4);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Fifth’,5);
GO

However this cannot be used if you want to insert multiple values into the table via your code because in this case you’ll have to write the code to insert the value in the DB in a for loop which is not at all feasible.

So you can write the code to generate the insert statement like,

USE YourDB
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

The above code inserts multiple values into DB with a single SQL insert query. This is the best means to use in your code as you’ll just have to create this query in your for loop and the query will run only once.

Also you can use the following query syntax for multiple inserts in SQL 2008

USE YourDB
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second‘,2),
(‘Third‘,3),
(‘Fourth‘,4),
(‘Fifth‘,5)

Please let me know if you have any better ideas than this one.
Njoy coding!!!