How can i select the other 25% of Records?

[Mota]

Hi;
We want to devide records of a table to 4 equal parts and in each step work
on one part of them.For now,i can just select the first part,using this
query:
"Select Top 25 percent [RecID],[Name],[Spec] from PrescribersTBL order by
RecID"
Where field RecID is the Primary Key.
How to select the 2nd,3rd and 4th part (25 percent) of this table?(that
needs 3 more queries)
Any help is so much appreciated.
Thank you.

 

[Tom Ellison]

Dear Mota:

The second quarter of the rows could be found as the last half of the first
half:

Select Top 50 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

The third quarter would be approximately:

Select Top 33 percent *
FROM (SELECT Top 75 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

and the last:

Select Top 25 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

Now, there are problems with this. The proportions are not exact. 33
percent is not exactly a third. It may omit some rows, especially if there
are many. Indeed, I would not want to guarantee that even 25 percent will
be 1/4 of the records. If the total number or rows do not divide exactly by
4 then you may still have gaps.

I recommend you first count the rows, then divide them into nearly equal
parts. Then use Top 999, where 999 is the number of rows instead of a
proportion. By taking the top 999 rows then the "bottom" 998 rows out of
the top 1997 rows, then the "bottom" 998 rows out of the top 2995 rows, then
the bottom 998 rows, you will have divided 3993 rows into four nearly equal
sets. You would have to control this with your own code so it comes out,
writing the SQL with the code and submitting the query text.

Not simple, but it's the best I know.

Tom Ellison

 

[Mota]

Dear Tom;
Such an accuracy in row counts is not important for us.What is more
important is avoiding repeated records in quarters.I tried the SQLs.With
this good clue you commented,beside my main purpose that i got from ur
answer,i have just 2 small problems:
1-The last record in 1st quarter is the first one (in fact the last one
because the order is Desc) in the 2nd quarter.Im afraid this problem exists
even using numbers (999) instead of percentage.
2-Unlike the others,your last query is not right.It returns about a half of
25% of records from the middle of my Table.Actually it selects a part of the
first 50%,not the last one.
In addition,how to get the "Bottom" 998 rows of a table?
Do u have any solution?Thank you for ur help.

Dear Mota:

The second quarter of the rows could be found as the last half of the
first half:

Select Top 50 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

The third quarter would be approximately:

Select Top 33 percent *
FROM (SELECT Top 75 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

and the last:

Select Top 25 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

Now, there are problems with this. The proportions are not exact. 33
percent is not exactly a third. It may omit some rows, especially if
there are many. Indeed, I would not want to guarantee that even 25
percent will be 1/4 of the records. If the total number or rows do not
divide exactly by 4 then you may still have gaps.

I recommend you first count the rows, then divide them into nearly equal
parts. Then use Top 999, where 999 is the number of rows instead of a
proportion. By taking the top 999 rows then the "bottom" 998 rows out of
the top 1997 rows, then the "bottom" 998 rows out of the top 2995 rows,
then the bottom 998 rows, you will have divided 3993 rows into four nearly
equal sets. You would have to control this with your own code so it comes
out, writing the SQL with the code and submitting the query text.

Not simple, but it's the best I know.

Tom Ellison

Hi;
We want to devide records of a table to 4 equal parts and in each step
work on one part of them.For now,i can just select the first part,using
this query:
"Select Top 25 percent [RecID],[Name],[Spec] from PrescribersTBL order by
RecID"
Where field RecID is the Primary Key.
How to select the 2nd,3rd and 4th part (25 percent) of this table?(that
needs 3 more queries)
Any help is so much appreciated.
Thank you.

 

[Tom Ellison]

Dear Mota:

I suspected there could be an overlap using percentages, but there would not
be an overlap using a counted TOP unless the table is changed between the
two queries. That is, there wouldn't be an overlap if:

- the counts are selected correctly

- the ORDER BY is "unique"

If your RecID is an autonumber then the only thing to suspect is the counts.
How many rows were in the table? How did you select them throughout the set
of queries?

The final query should have just been the TOP 25 percent of the whole table,
reverse sorted:

SELECT TOP 25 PERCENT *
FROM PrescribersTBL
ORDER BY RecID DESC

OR

SELECT TOP 998 *
FROM PrescribersTBL
ORDER BY RecID DESC

Sorry about that. Perhaps too close to my bedtime!

If you will study how I've done this, perhaps by drawing a diagram of your
table (just a big rectangle) then take the top half, then the bottom half of
the top half, you'll see how that is the second quarter (except for rounding
errors, which is why the counted records version of TOP should be much
better). With that knowledge, you may be able to craft just what you need.

It is not only overlapping that could be a concern, but gaps between.

Tom Ellison

Dear Tom;
Such an accuracy in row counts is not important for us.What is more
important is avoiding repeated records in quarters.I tried the SQLs.With
this good clue you commented,beside my main purpose that i got from ur
answer,i have just 2 small problems:
1-The last record in 1st quarter is the first one (in fact the last one
because the order is Desc) in the 2nd quarter.Im afraid this problem
exists even using numbers (999) instead of percentage.
2-Unlike the others,your last query is not right.It returns about a half
of 25% of records from the middle of my Table.Actually it selects a part
of the first 50%,not the last one.
In addition,how to get the "Bottom" 998 rows of a table?
Do u have any solution?Thank you for ur help.

Dear Mota:

The second quarter of the rows could be found as the last half of the
first half:

Select Top 50 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

The third quarter would be approximately:

Select Top 33 percent *
FROM (SELECT Top 75 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

and the last:

Select Top 25 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

Now, there are problems with this. The proportions are not exact. 33
percent is not exactly a third. It may omit some rows, especially if
there are many. Indeed, I would not want to guarantee that even 25
percent will be 1/4 of the records. If the total number or rows do not
divide exactly by 4 then you may still have gaps.

I recommend you first count the rows, then divide them into nearly equal
parts. Then use Top 999, where 999 is the number of rows instead of a
proportion. By taking the top 999 rows then the "bottom" 998 rows out of
the top 1997 rows, then the "bottom" 998 rows out of the top 2995 rows,
then the bottom 998 rows, you will have divided 3993 rows into four
nearly equal sets. You would have to control this with your own code so
it comes out, writing the SQL with the code and submitting the query
text.

Not simple, but it's the best I know.

Tom Ellison

Hi;
We want to devide records of a table to 4 equal parts and in each step
work on one part of them.For now,i can just select the first part,using
this query:
"Select Top 25 percent [RecID],[Name],[Spec] from PrescribersTBL order
by RecID"
Where field RecID is the Primary Key.
How to select the 2nd,3rd and 4th part (25 percent) of this table?(that
needs 3 more queries)
Any help is so much appreciated.
Thank you.

 

[Mota]

Thank you Tom.
You are right.Calculating relative counts in code and using them in queries
rather than percentage,fully resolves overlaping problem.In
addition,selecting the last quarter was as simple as both ur SQL sentences
below !
Excuse me for disturbing your bedtime.
Thanx again for ur help.

Dear Mota:

I suspected there could be an overlap using percentages, but there would
not be an overlap using a counted TOP unless the table is changed between
the two queries. That is, there wouldn't be an overlap if:

- the counts are selected correctly

- the ORDER BY is "unique"

If your RecID is an autonumber then the only thing to suspect is the
counts. How many rows were in the table? How did you select them
throughout the set of queries?

The final query should have just been the TOP 25 percent of the whole
table, reverse sorted:

SELECT TOP 25 PERCENT *
FROM PrescribersTBL
ORDER BY RecID DESC

OR

SELECT TOP 998 *
FROM PrescribersTBL
ORDER BY RecID DESC

Sorry about that. Perhaps too close to my bedtime!

If you will study how I've done this, perhaps by drawing a diagram of your
table (just a big rectangle) then take the top half, then the bottom half
of the top half, you'll see how that is the second quarter (except for
rounding errors, which is why the counted records version of TOP should be
much better). With that knowledge, you may be able to craft just what you
need.

It is not only overlapping that could be a concern, but gaps between.

Tom Ellison

Dear Tom;
Such an accuracy in row counts is not important for us.What is more
important is avoiding repeated records in quarters.I tried the SQLs.With
this good clue you commented,beside my main purpose that i got from ur
answer,i have just 2 small problems:
1-The last record in 1st quarter is the first one (in fact the last one
because the order is Desc) in the 2nd quarter.Im afraid this problem
exists even using numbers (999) instead of percentage.
2-Unlike the others,your last query is not right.It returns about a half
of 25% of records from the middle of my Table.Actually it selects a part
of the first 50%,not the last one.
In addition,how to get the "Bottom" 998 rows of a table?
Do u have any solution?Thank you for ur help.

Dear Mota:

The second quarter of the rows could be found as the last half of the
first half:

Select Top 50 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

The third quarter would be approximately:

Select Top 33 percent *
FROM (SELECT Top 75 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

and the last:

Select Top 25 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

Now, there are problems with this. The proportions are not exact. 33
percent is not exactly a third. It may omit some rows, especially if
there are many. Indeed, I would not want to guarantee that even 25
percent will be 1/4 of the records. If the total number or rows do not
divide exactly by 4 then you may still have gaps.

I recommend you first count the rows, then divide them into nearly equal
parts. Then use Top 999, where 999 is the number of rows instead of a
proportion. By taking the top 999 rows then the "bottom" 998 rows out
of the top 1997 rows, then the "bottom" 998 rows out of the top 2995
rows, then the bottom 998 rows, you will have divided 3993 rows into
four nearly equal sets. You would have to control this with your own
code so it comes out, writing the SQL with the code and submitting the
query text.

Not simple, but it's the best I know.

Tom Ellison



Hi;
We want to devide records of a table to 4 equal parts and in each step
work on one part of them.For now,i can just select the first part,using
this query:
"Select Top 25 percent [RecID],[Name],[Spec] from PrescribersTBL order
by RecID"
Where field RecID is the Primary Key.
How to select the 2nd,3rd and 4th part (25 percent) of this table?(that
needs 3 more queries)
Any help is so much appreciated.
Thank you.

 

[Tom Ellison]

Dear Mota:

You didn't "disturb my bedtime" at all. Rather, I choose to continue
working after I got groggy. I was so close to finishing the answer to your
post!

It surely sounds like you've caught onto what I wanted to explain to you.
Enjoy!

Tom Ellison

Thank you Tom.
You are right.Calculating relative counts in code and using them in
queries rather than percentage,fully resolves overlaping problem.In
addition,selecting the last quarter was as simple as both ur SQL sentences
below !
Excuse me for disturbing your bedtime.
Thanx again for ur help.

Dear Mota:

I suspected there could be an overlap using percentages, but there would
not be an overlap using a counted TOP unless the table is changed between
the two queries. That is, there wouldn't be an overlap if:

- the counts are selected correctly

- the ORDER BY is "unique"

If your RecID is an autonumber then the only thing to suspect is the
counts. How many rows were in the table? How did you select them
throughout the set of queries?

The final query should have just been the TOP 25 percent of the whole
table, reverse sorted:

SELECT TOP 25 PERCENT *
FROM PrescribersTBL
ORDER BY RecID DESC

OR

SELECT TOP 998 *
FROM PrescribersTBL
ORDER BY RecID DESC

Sorry about that. Perhaps too close to my bedtime!

If you will study how I've done this, perhaps by drawing a diagram of
your table (just a big rectangle) then take the top half, then the bottom
half of the top half, you'll see how that is the second quarter (except
for rounding errors, which is why the counted records version of TOP
should be much better). With that knowledge, you may be able to craft
just what you need.

It is not only overlapping that could be a concern, but gaps between.

Tom Ellison

Dear Tom;
Such an accuracy in row counts is not important for us.What is more
important is avoiding repeated records in quarters.I tried the SQLs.With
this good clue you commented,beside my main purpose that i got from ur
answer,i have just 2 small problems:
1-The last record in 1st quarter is the first one (in fact the last one
because the order is Desc) in the 2nd quarter.Im afraid this problem
exists even using numbers (999) instead of percentage.
2-Unlike the others,your last query is not right.It returns about a half
of 25% of records from the middle of my Table.Actually it selects a part
of the first 50%,not the last one.
In addition,how to get the "Bottom" 998 rows of a table?
Do u have any solution?Thank you for ur help.

Dear Mota:

The second quarter of the rows could be found as the last half of the
first half:

Select Top 50 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

The third quarter would be approximately:

Select Top 33 percent *
FROM (SELECT Top 75 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

and the last:

Select Top 25 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

Now, there are problems with this. The proportions are not exact. 33
percent is not exactly a third. It may omit some rows, especially if
there are many. Indeed, I would not want to guarantee that even 25
percent will be 1/4 of the records. If the total number or rows do not
divide exactly by 4 then you may still have gaps.

I recommend you first count the rows, then divide them into nearly
equal parts. Then use Top 999, where 999 is the number of rows instead
of a proportion. By taking the top 999 rows then the "bottom" 998 rows
out of the top 1997 rows, then the "bottom" 998 rows out of the top
2995 rows, then the bottom 998 rows, you will have divided 3993 rows
into four nearly equal sets. You would have to control this with your
own code so it comes out, writing the SQL with the code and submitting
the query text.

Not simple, but it's the best I know.

Tom Ellison



Hi;
We want to devide records of a table to 4 equal parts and in each step
work on one part of them.For now,i can just select the first
part,using this query:
"Select Top 25 percent [RecID],[Name],[Spec] from PrescribersTBL order
by RecID"
Where field RecID is the Primary Key.
How to select the 2nd,3rd and 4th part (25 percent) of this
table?(that needs 3 more queries)
Any help is so much appreciated.
Thank you.

 

[Mota]

Sure.
Thanx anyway.

Dear Mota:

You didn't "disturb my bedtime" at all. Rather, I choose to continue
working after I got groggy. I was so close to finishing the answer to
your post!

It surely sounds like you've caught onto what I wanted to explain to you.
Enjoy!

Tom Ellison

Thank you Tom.
You are right.Calculating relative counts in code and using them in
queries rather than percentage,fully resolves overlaping problem.In
addition,selecting the last quarter was as simple as both ur SQL
sentences below !
Excuse me for disturbing your bedtime.
Thanx again for ur help.

Dear Mota:

I suspected there could be an overlap using percentages, but there would
not be an overlap using a counted TOP unless the table is changed
between the two queries. That is, there wouldn't be an overlap if:

- the counts are selected correctly

- the ORDER BY is "unique"

If your RecID is an autonumber then the only thing to suspect is the
counts. How many rows were in the table? How did you select them
throughout the set of queries?

The final query should have just been the TOP 25 percent of the whole
table, reverse sorted:

SELECT TOP 25 PERCENT *
FROM PrescribersTBL
ORDER BY RecID DESC

OR

SELECT TOP 998 *
FROM PrescribersTBL
ORDER BY RecID DESC

Sorry about that. Perhaps too close to my bedtime!

If you will study how I've done this, perhaps by drawing a diagram of
your table (just a big rectangle) then take the top half, then the
bottom half of the top half, you'll see how that is the second quarter
(except for rounding errors, which is why the counted records version of
TOP should be much better). With that knowledge, you may be able to
craft just what you need.

It is not only overlapping that could be a concern, but gaps between.

Tom Ellison


Dear Tom;
Such an accuracy in row counts is not important for us.What is more
important is avoiding repeated records in quarters.I tried the
SQLs.With this good clue you commented,beside my main purpose that i
got from ur answer,i have just 2 small problems:
1-The last record in 1st quarter is the first one (in fact the last one
because the order is Desc) in the 2nd quarter.Im afraid this problem
exists even using numbers (999) instead of percentage.
2-Unlike the others,your last query is not right.It returns about a
half of 25% of records from the middle of my Table.Actually it selects
a part of the first 50%,not the last one.
In addition,how to get the "Bottom" 998 rows of a table?
Do u have any solution?Thank you for ur help.

Dear Mota:

The second quarter of the rows could be found as the last half of the
first half:

Select Top 50 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

The third quarter would be approximately:

Select Top 33 percent *
FROM (SELECT Top 75 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

and the last:

Select Top 25 percent *
FROM (SELECT Top 50 percent [RecID],[Name],[Spec]
from PrescribersTBL order by RecID)
ORDER BY RecID DESC

Now, there are problems with this. The proportions are not exact. 33
percent is not exactly a third. It may omit some rows, especially if
there are many. Indeed, I would not want to guarantee that even 25
percent will be 1/4 of the records. If the total number or rows do
not divide exactly by 4 then you may still have gaps.

I recommend you first count the rows, then divide them into nearly
equal parts. Then use Top 999, where 999 is the number of rows
instead of a proportion. By taking the top 999 rows then the "bottom"
998 rows out of the top 1997 rows, then the "bottom" 998 rows out of
the top 2995 rows, then the bottom 998 rows, you will have divided
3993 rows into four nearly equal sets. You would have to control this
with your own code so it comes out, writing the SQL with the code and
submitting the query text.

Not simple, but it's the best I know.

Tom Ellison



Hi;
We want to devide records of a table to 4 equal parts and in each
step work on one part of them.For now,i can just select the first
part,using this query:
"Select Top 25 percent [RecID],[Name],[Spec] from PrescribersTBL
order by RecID"
Where field RecID is the Primary Key.
How to select the 2nd,3rd and 4th part (25 percent) of this
table?(that needs 3 more queries)
Any help is so much appreciated.
Thank you.