Basically, a simple example is this:
SELECT * FROM tablename
WHERE field >= 50 AND field <= 100
But if you're retrieving a range of data like 50th, 241st, 332nd, etc... you have to eliminate the 2 characters after the number first (th, nd, st, etc...). See example below.
SELECT * FROM table
WHERE LEFT(field, (LEN(field) - 2)) >= 50 AND
left(field, (LEN(field) - 2)) <= 100
I believe you are asking "How do I return the 50th through 100th record of a result set?" Potentially for paging reasons, etc. Kind of like using the "TOP" keyword in a SELECT statement, but you want some out of the middle instead of from the top...
You could use a cursor, but you could also use a temp table that has an identity column defined. Of course, if you have a ton of data this may be bad, but then a cursor would be bad too.
This is what I've done. I'm not sure I like it, but it worked.
Create Procedure NewStoredProcedureName
@Min_Record integer,
@Max_Record integer
AS
INSERT INTO #Temp (Field1, Field2, Field3)
SELECT t.Field1, t.Field2, t.Field3
FROM Table t
ORDER BY t.Field1
SELECT Field1, Field2, Field3
FROM #Temp
WHERE (Record_Number >= @Min_Record
and Record_Number <= @Max_Record)
ORDER BY Record_Number
If you want to get record 50 to 100 and then get record 100 to 150, the Order by clause in the insert/select is extremely important so the identity field value is assigned to the same record each time.
Thanks for your answers.I still have problem about it.
If I have a table which contains 100,000,000 records,but I only want to get 20 records once,such as from 100,500th to 100,520th.Use temp table,I must first select at least 100,520 records, it's terrible,right?
Can you point out what different between temp table and cursor? What method should I use? Run fast is what I care.
I'd rather select 100,000,000 records into a temp table then declare a cursor, loop through each one, setting variables along the way. In my experience, if you can get away from using cursors, do it. SQL Server isn't very efficient at looping. Then again, if you have 100,000,000 records, you may want to think about doing something so you don't have to do that sort of thing at run-time. If the data is static, you could put the identity field on the table itself and write something to insure that the field is kept sequential.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.