| Author |
 Topic  |
|
Etymon
Advanced Member
United States
2395 Posts |
 Posted - 11 June 2007 : 10:06:26
|
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. 
|
Edited by - Etymon on 11 June 2007 10:10:37 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 June 2007 : 10:09:50
|
Use a SELECT TOP within another SELECT TOP, such as in
SELECT TOP 100 * FROM (
SELECT TOP 200 * ORDER BY ID ASC
) As Temp
ORDER BY ID DESC
This will get you the middle block. To get the last block, just change the 200 to 300.
This should work in SQL Server and maybe Access, but the Access syntax may need a little tweaking. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 11 June 2007 : 10:13:33
|
Awesome! Thanks Rui.
I just needed it for MS SQL.  |
Edited by - Etymon on 11 June 2007 10:14:31 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 11 June 2007 : 10:28:28
|
Ah gee! Hi again Rui ... I hate to bother you,
I'm having trouble with the query. For now I'm using the query in Visual Studio 2005. I chose to try it out in the FORUM_A_REPLY table, but I get errors.
|
Edited by - Etymon on 11 June 2007 10:29:40 |
|
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 11 June 2007 : 12:37:06
|
Ok, I got it to return results, but I'm still having trouble getting it to return results unique to a second grouping of 100.
My first query is:
SELECT TOP 100 TOPIC_ID
FROM FORUM_TOPICS
ORDER BY TOPIC_ID
My second query is:
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
Both queries are returning the same results for me.
Am I doing something wrong?
Thanks again, Rui. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 June 2007 : 13:16:38
|
| Look at the DESC and ASC in my code. They are very important to get the results you want. Use them just as I did - ASC in the internal query, DESC in the external query. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 11 June 2007 : 13:46:46
|
Right! I see!
When I checked the query in Visual Studio, ASC (understood as a default keyword) was removed by the syntax checker when I used "Verify SQL Syntax" against the query ... resulting in:
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
I was wanting the results to list from smallest to largest ID, so I put ASC instead of DESC on the last line. I altered this keyword first and then executed the query before executing the query using DESC.
Thanks again Rui!
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 June 2007 : 14:55:00
|
| If you want the results in another order, you need ANOTHER outer query, just to order the records them again... The order in the two existing queries is important to get the desired records. You can't change it, or you won't get the 2nd 100 records block. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 11 June 2007 : 15:43:33
|
Help??!  |
Edited by - Etymon on 11 June 2007 16:14:49 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 June 2007 : 15:59:40
|
SELECT * FROM (
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
) As T2
Order by TOPIC_ID ASC;
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 11 June 2007 : 16:16:08
|
| Sehr Gute! Danke! Danke! (Very Good! Thanks! Thanks!) |
Edited by - Etymon on 11 June 2007 17:25:30 |
|
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 12 June 2007 : 11:25:03
|
Hmmm ...
Next problem.
If I have say 150+ Topic IDs and I select the second group of 100 ...
The problem I am having is that the second query for the second set of 100 is selecting the Topic IDs from largest to smallest and then the outer query is selecting from largest to smallest and then sorting them from smallest to largest. However, the first query for the first 100 is selecting from smallest to largest and sorting from smallest to largest. This is creating a union of the two sets of 100 creating an overlap in returned Topic IDs.
How do I work around the union? |
Edited by - Etymon on 12 June 2007 11:54:39 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 12 June 2007 : 14:41:13
|
That happens because you don't have 200 different topics? If so, you need to get the first hundred and then just the rest, not one hundred again...
Anyway, why can't you, somehow, use a temporary table or a field in the same table or another table to signal the records you have previously selected? It would be way much easier. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Etymon
Advanced Member
United States
2395 Posts |
Posted - 12 June 2007 : 14:47:46
|
Hmmmm. That does sound easier.
Did you get the email that I sent to you through the forums? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Topic |
|
Topic Locked
