Should Implement ROW_NUMBER() method.

Just take this Example.

Create table emp(empid int, ename varchar(50),salary numeric
(9,2)

Insert into emp values(1,'Employee1',10000)
Insert into emp values(2,'Employee2',20000)
.
.
Insert into emp values(10,'Employee10',100000)

   Consider the above table which have 10 records. Now u 
want to select 5,6,7 Rows in this table. Just try this 
query.

SELECT E.EmpID,E.EName,E.Salary FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY EmpID ASC) AS Rno, * 
FROM   emp
) E
WHERE E.Rno >= 5 and E.Rno <= 7

Thats all.
If anyone have other such good idea kindly share........

You can achieve this by using following Query

select * from(select top 3 * from employees where 
employeeid in(select  top 7 employeeid from employees where 
employeeid not in(select top 4 employeeid from employees 
order by employeeid)))e order by employeeid.

Thanks
AyyappanRamachandran

select * from 
       (select top 3 * from emp where empid in
            (select top 7 * from emp where empid not in 
   
(select top 4 * from emp order by empid)) order by empid

select * from emp where EmpID between 5 and 7

select rnum, d.* from dept d, (select rownum rnum , deptno 
from dept ) e
where d.deptno = e.deptno and rnum between 5 and 7;

select * from emp where empid >=5 and empid <=7

CREATE TABLE Names 
( 
    NameID INT IDENTITY(1, 1) 
        PRIMARY KEY CLUSTERED, 
    FName VARCHAR(32) 
) 
GO 
 
SET NOCOUNT ON 
INSERT Names(FName) VALUES('Aaron') 
INSERT Names(FName) VALUES('Greg') 
INSERT Names(FName) VALUES('Alex') 
INSERT Names(FName) VALUES('Luan') 
INSERT Names(FName) VALUES('John') 
INSERT Names(FName) VALUES('Todd') 
INSERT Names(FName) VALUES('Scott') 
INSERT Names(FName) VALUES('Jess') 
INSERT Names(FName) VALUES('Drew') 
INSERT Names(FName) VALUES('Katherine') 
INSERT Names(FName) VALUES('Paul') 
GO 
 
-- solution #1: nested top 
 
SELECT TOP 1 FName 
FROM 
( 
    SELECT TOP 10 FName 
    FROM Names 
    ORDER BY FName 
) sub 
ORDER BY FName DESC 
 
-- solution #2: NOT IN 
 
SELECT TOP 1 FName  
FROM Names WHERE FName NOT IN 
( 
    SELECT TOP 9 FName 
    FROM Names 
    ORDER BY FName 
) 
ORDER BY FName 
 
-- solution #3: derived count 
-- this assumes FName is unique 
 
SELECT FName 
    FROM Names 
    WHERE  
    ( 
        SELECT COUNT(*) 
        FROM Names n2 
        WHERE n2.FName <= Names.FName 
    ) = 10 
 
-- solution #4: MAX 
 
SELECT FName = MAX(FName) FROM  
( 
    SELECT TOP 10 FName 
    FROM Names 
    ORDER BY FName 
) sub 
 
-- solution #5: relative fetch from cursor 
 
-- yes, cursors are generally evil, but 
-- sometimes you might be surprised 
 
DECLARE FNames CURSOR 
    LOCAL STATIC READ_ONLY FOR 
    SELECT FName 
        FROM Names 
        ORDER BY FName 
 
DECLARE @FName VARCHAR(32) 
 
OPEN FNames 
 
FETCH RELATIVE 10 FROM FNames INTO @FName 
 
CLOSE FNames 
DEALLOCATE FNames 
 
SELECT FName = @FName 
 
DROP TABLE Names 
GO

select * from (select row_number() over (order by empid) as 
row, *
from employee)a
where a.row between 4 and 5

If you knowfor sure that the number of rows is 10 then the 
above answers given above a great examples.  So what if you 
don't know the total number of rows in that table?  Can you 
use Select count(*) from nameoftable;

select * from emp where sno between 5 and 7









ajay.thomala@gmail.com

i have other idea if there is no duplicate record in the 
table you can use this


select top 7 *from employee
except
select top 4 *from employee

Please ignore my above answer

select * from tablename where sno between 4 and 8


Ajay

select *  from ( select rownum r, comp_name from metatest )
where r >  4 and  r < 8;

select top 3* from Tablename 
where columnname in ( select top 7columnname from table 
name order by columnname desc )
and  columnname not in( select top 4columnname from 
tablename order by columnname desc )
order by  columnname desc