Selecting from the middle of a recordset

[wvmikep]

I want to be able to duplicate, in some fashion, what I can do in mySQL. In mySQL, I can use the LIMIT statement to grab select data from the middle of a recordset. All I know of for SQL Server is to use SELECT TOP n. However, TOP doesn't help me here.

For example: A recordset has the following uniqueIDs:
1, 5, 8, 22, 78, 79, 82, 87, 90, 91

I want to grab the middle 6. However, in real life, I won't know, unless I query the database, what any of those values are.

 

[JamesLean]

If you don't know how many rows it's going to return then grabbing the "middle 6" is going to be more difficult. However, if you know you just want to grab six rows starting from the 3rd row then you can use:

SELECT TOP 6 id
FROM t1
WHERE id NOT IN (
  SELECT TOP 2 id
  FROM t1
  ORDER BY id
)
ORDER BY id

--James

 

[ClaireHCM]

Try this

select top 1 id from (select top 6 id from a order by id desc)a

 

[PHH1955]

Look in the MS-SQL help files for info about using a CURSOR. A CURSOR is a result set and you can do a variety of functions with it, like count records, etc. I'll see if I can come with some specific code to do what you are asking but I may not be able to get to it for a few days.

What version of SQL are you trying to use?

 

[wvmikep]

Re: JamesLean

DOH!!! That's so simple I'm ashamed I didn't think of that. However (and this might be unfounded), will performance become an issue doing it this way if the second TOP statement uses a large number (as in well over a couple hundred)?

BTW, I'm using SQL Server 7 right now.

 

[JamesLean]

Wouldn't think so. Adding an index on the id column may be a good idea if it hasn't already got one.

--James