how to select 5 to 7 rows from a table, which contains 10 rows?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
Answer / ajay
select * from emp where sno between 5 and 7
ajay.thomala@gmail.com
Is This Answer Correct ? | 2 Yes | 4 No |
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 |
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 |
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 |
Can I know,how to Execute Funcion and Trigger through command(Manualy Execution) in MS SQL/SERVER 2005,give me answer with Example.
What are the types of stored procedures in an sql server?
What do we need queues in sql service broker?
What stored procedure can you use to display the current processes?
List all types of constraints in sql server?
What is inline variable assignment?
Explain transaction isolation levels in sql server?
Explain about system stored procedure?
Explain the use of containers in ssis and also their types?
How to create dbo table in sql server?
What are the limitation of the Online Index Rebuild Operation?
Do you know what is sql injection?