How to get the nth row value of a table.

 Posted by dhirenkaunar-15094 on 4/20/2012 | Category: Sql Server Interview questions | Views: 2203 | Points: 40
Answer:

Problem : I have a employee table then how to find out nth employee detail with out using the actual id.

For the above question I have found the below solution.

The below solution I have wrote the query for , getting the record of third employee.

CREATE TABLE [dbo].[Employee](
[Id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NULL,
[Age] [int] NOT NULL,
[Photo] [image] NULL,
[Salary] [numeric](10, 2) NULL,
)

INSERT INTO Employee values (101,'James Clerk',29,NULL,1000.00);
INSERT INTO Employee values (102,'Steve Proell',40,NULL,60000.00);
INSERT INTO Employee values (103,'Matt Mcnair',35,NULL,5000.00);
INSERT INTO Employee values (104,'Amit Kr',29,NULL,1000.00);
INSERT INTO Employee values (105,'Jeff Yeary',32,NULL,1000.00);

# Solution 1
select  * from employee where id in (

select MAX(Id) from Employee
where Id in (select top(3) ID from Employee ))


# Solution 2

select top 1 *

from employee
where Id in (select top 3 Id from employee order by Id asc)
order by Id desc


# Solution 3

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, * FROM Employee) sub
WHERE RowNum = 3


Please suggest if any other solution are there for the above problem


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response

More Interview Questions by dhirenkaunar-15094