How to get record of nth row of a table ?

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

Problem : I have an employee table , and id is the primary key ,then how to find out third employee record

For the above problem I have found the below solutions :

How to get
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);

# Example 1 : Using Max()
select  * from employee where id in (

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


# Example 2 : Using Top() ,asc , desc

select top 1 *

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


#Example 3 : Using ROW_NUMBER()

SELECT * FROM

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

Please suggest if any more solution is there for this.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response

More Interview Questions by dhirenkaunar-15094