Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-12 : 08:37:35
|
jerry writes "Here's _THE_ question. It's plagued me off and on, most of my professional career. Let me setup the criteria.
1. The source table is on a non-clustered server.
2. The source table has more than 4,000,000 rows.
3. This is SQL 2000.
4. the id.column is NOT ordered.
I want to be able to craft a select statement that will go into a table and based on the number of of rows I specifiy, say 40, return that number of records. But, wait, that's not all (that's too easy) I want the records to not come from the top of the table, but start from the 1,234,567th row. Then allow me to sort the table however i want with any column.
EG: Select startRow 1,234,567 rowCount 40 * from table
This make sense? Let's look at it in little numbers for a second. Let's say you want the top 20 records, but the next "page" would be between 20 and 40, ya know? But you can't use between, because there is no ordered key.
EG: Select startRow 20 rowCount 20 * from table
Here's the best I have come up with, but I can't stop thinking this is a kludge.
DECLARE @SOURCEKEYFIELD varChar(255),
@SOURCETABLE varChar(255),
@TOPNUM integer,
@BOTTOMNUM integer
SET NOCOUNT ON
CREATE TABLE #tempTable
(
tempPK int IDENTITY (1, 1),
sourcePK char(9)
)
INSERT #tempTable (sourcePK)
SELECT TOP @TOPNUM @SOURCEKEYFIELD
FROM @SOURCETABLE
SELECT s.*
FROM #tempTable t
JOIN @SOURCETABLE s ON t.sourcePK = s.[@SOURCEKEYFIELD]
WHERE (t.tempPK BETWEEN @BOTTOMNUM AND @TOPNUM)
DROP TABLE #tempTable
SET NOCOUNT OFF" |
|
|
|
|
|
|
|
accessing a specified block in a table