x

Need best query to select Rows 6 to 8 from a table containing 10 rows

can anybody provide me cost effective query for my title question,also im not expecting cursors in this context. thanks in advance.

more ▼

asked Mar 20, 2010 at 01:47 AM in Default

avatar image

venkatreddy
562 29 31 36

its OK, we weren't expecting cursors either

Mar 20, 2010 at 07:16 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

do you mean a random set of 6,7 or 8 rows from your 10?

select top 6 column from table

or rows 6,7 and 8 from the original 10? (you need to specify what ordering should be used)

---get the bottom 3 (6,7,8) from the top 8
select top 3 column 
from
   (select top 8 column from table order by column asc) tablealias
order by column desc

or if you are using SQL2005 and above, you can use row_number() (again need to define an order)

select column
from (
     select row_number()over (order by column) as rownum, column
     from table
    ) alias
where rownum in (6,7,8)

or use a common table expression (cte)

;with cte (rownum,column) as ( select row_number()over (order by column) as rownum, column from table )

select column from cte where rownum in (6,7,8)

more ▼

answered Mar 20, 2010 at 05:59 AM

avatar image

Kev Riley ♦♦
65.8k 48 63 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1084
x21

asked: Mar 20, 2010 at 01:47 AM

Seen: 2226 times

Last Updated: Mar 20, 2010 at 06:48 AM

Copyright 2017 Redgate Software. Privacy Policy