how to select 5 to 7 rows from a table, which contains 10 rows?

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / durga prasad

21 Answers
26363 Views
IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / purabi roy(sarkar)

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

Is This Answer Correct ?

0 Yes

1 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / tk

select * from Tablename limit 5,7 ;

Is This Answer Correct ?

1 Yes

2 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / govind

select * from emp where empid between 5 and 7

Is This Answer Correct ?

0 Yes

1 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / guest

select * from emp where EmpID between 5 and 7

Is This Answer Correct ?

7 Yes

9 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / rakesh kumar jaiswal

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

Is This Answer Correct ?

1 Yes

3 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / john t

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;

Is This Answer Correct ?

0 Yes

2 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / ajay

select * from emp where sno between 5 and 7

ajay.thomala@gmail.com

Is This Answer Correct ?

2 Yes

4 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / monty

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

Is This Answer Correct ?

1 Yes

3 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / ajay (esd)

Please ignore my above answer

select * from tablename where sno between 4 and 8

Ajay

Is This Answer Correct ?

0 Yes

2 No

how to select 5 to 7 rows from a table, which contains 10 rows?..

Answer / manas

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

Is This Answer Correct ?

1 Yes

5 No

Post New Answer