record groups in blocks via a query

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

record groups in blocks via a query

Author

Topic

Etymon
Starting Member

6 Posts

Posted - 2007-06-11 : 11:35:18

I'm trying to place records into groups of 100, so I need a query that will return records but not based upon an autonumber type field. For instance, if I had a set of records that had been sequentially numbered (by autonumber) with IDs to greater than 3,600 but there were actually only 300 IDs remaining ranging from 1 to 3600 (say the others were deleted), then I would want to use a query to make three groups of 100 of the remaining IDs.

I can use SELECT TOP 100 for the first 100 records. How do I get subsequent groups of 100?

It's probably obvious and I am thinking too hard.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 11:48:05

Next 100


select top 100 * from (select top 200 * from tbl order by ID) a order by ID desc

Etymon
Starting Member

6 Posts

Posted - 2007-06-11 : 12:35:15

Thank you khtan!

I seem to be having trouble with getting the results I want.

I'm running this against a Snitz board. I appear to be getting the same results from each query. Here are my queries ...

Query #1:

SELECT TOP 100 TOPIC_ID
FROM FORUM_TOPICS
ORDER BY TOPIC_ID

Results from Query #1:

TOPIC_ID
6614
6666
6673
6721
6723
6725
6733
6769
6775
6814
6837
6867
6871
6902
6928
6930
6937
6938
7008
7009
7030
7032
7045
7046
7047
7048
7052
7053
7054
7074
7081
7085
7103
7104
7105
7109
7112
7141
7151
7171
7188
7190
7193
7195
7216
7283
7285
7297
7310
7316
7339
7346
7353
7360
7399
7429
7470
7495
7516
7576
7634
7702
7714
7745
7746
7864
7913
7964
8007
8016
8201
8252
8308
8320
8348
8380
8386
8410
8439
8540
8647
8651
8661
8665
8782
8840
8862
8914
8917
8962
8968
8977
9024
9068
9333
9335
9344
9513
9567
9710

Query #2:

SELECT TOP 100 TOPIC_ID
FROM (SELECT TOP 200 TOPIC_ID
FROM FORUM_TOPICS AS NEXT_200_TOPICS
ORDER BY TOPIC_ID) AS FORUM_TOPICS
ORDER BY TOPIC_ID

Results from Query #2:

TOPIC_ID
6614
6666
6673
6721
6723
6725
6733
6769
6775
6814
6837
6867
6871
6902
6928
6930
6937
6938
7008
7009
7030
7032
7045
7046
7047
7048
7052
7053
7054
7074
7081
7085
7103
7104
7105
7109
7112
7141
7151
7171
7188
7190
7193
7195
7216
7283
7285
7297
7310
7316
7339
7346
7353
7360
7399
7429
7470
7495
7516
7576
7634
7702
7714
7745
7746
7864
7913
7964
8007
8016
8201
8252
8308
8320
8348
8380
8386
8410
8439
8540
8647
8651
8661
8665
8782
8840
8862
8914
8917
8962
8968
8977
9024
9068
9333
9335
9344
9513
9567
9710

Etymon
Starting Member

6 Posts

Posted - 2007-06-11 : 13:38:14

Ah!!! I found my error!

I had removed desc from the query

SELECT TOP 100 TOPIC_ID
FROM (SELECT TOP 200 TOPIC_ID
FROM FORUM_TOPICS AS NEXT_200_TOPICS
ORDER BY TOPIC_ID) AS FORUM_TOPICS
ORDER BY TOPIC_ID DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources