Selecting the top 1/3 of a table

[Guest]

I have a table with 306 records. If I select the top 33.333333333 percent,
it's returning 101 rows. It should return 102 rows.

Thinking that this was possibly a round-off error...I tried selecting the
top 99 percent, and it returned 303 rows. When 306 * 0.99 = 302.94, I'm now
not too sure it's truly a round-off error...because... 306 * 0.33333333333 =
101.99999, which if the row count were rounded up like the top 99 percent, it
should return 102.

Does anyone have any suggestions?

 

[John Spencer]

I'm guessing that Access is only accepting the number before the decimal
point for figuring out the percentage. 33 percent of 306 is 100.98 (101).

Is it a bug? Or is it not documented fully? I would have liked an error
message that says that complained about the number not being an integer, but
that doesn't seem to be the case.

 

[Guest]

Yeah...I had posted on another website this same question, and just about an
hour or two ago someone told me that it only accepts integer values for the
percentage...which pretty much leaves everyone out who wants 1/3, 1/7, 1/11,
1/13, 1/17, 1/19, etc. of the data.

An error message would have been nice...but then again...it still wouldn't
solve the problem I have...but just let me know sooner what was going on.

If only Microsoft implemented a row counter directly into the query language
(like Oracle's ROWCOUNT), that would make everyones life so much easier. Or
better yet, scrap Access altogether, and rebuild it on top of their light SQL
server. (and VB macros need to be stripped out of Office altogether, and
upgraded to .NET)

Well, now that I've had my little rant...lol...I thank you for attempting to
help me out. If you happen to know of anything that may help (and doesn't
use macros), please let me know.

 

[Guest]

I was put in SELECT TOP 33.4 PERCENT and return 101 records when sorting on
the primary key.

When I typed in SELECT TOP 33.5 PERCENT the query returned 105 records.
Looks like it does round the percent.

 

[Jamie Collins]

I was put in SELECT TOP 33.4 PERCENT and return 101 records when sorting on
the primary key.

When I typed in SELECT TOP 33.5 PERCENT the query returned 105 records.
Looks like it does round the percent.

One obvious approach, then, is to spurn the proprietary TOP N syntax,
over which no one has any control, and replace it will standard SQL
syntax tailored to the OP's needs e.g.

SELECT T1.data_col
FROM Test AS T1
WHERE (
SELECT COUNT(*) * 0.3333333333333333333333333
FROM Test AS T3
) >= (
SELECT COUNT(*)
FROM Test AS T2
WHERE T1.data_col >= T2.data_col
);

Here's some VBA to reproduce a demo to return 102 rows from a set of
306 rows using the parameter value 0.3333333333333333333333333:

Sub test306()
Kill "C:\DropMe1.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe1.mdb"
With .ActiveConnection

' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Digits (nbr INTEGER NOT NULL" & _
" UNIQUE, CHECK(nbr BETWEEN 0 AND 9));"

.Execute _
"INSERT INTO Digits (nbr) VALUES (0);"

.Execute _
"INSERT INTO Digits (nbr) SELECT DT1.nbr" & _
" FROM (SELECT 1 AS nbr FROM Digits UNION" & _
" ALL SELECT 2 FROM Digits UNION ALL SELECT" & _
" 3 FROM Digits UNION ALL SELECT 4 FROM Digits" & _
" UNION ALL SELECT 5 FROM Digits UNION ALL" & _
" SELECT 6 FROM Digits UNION ALL SELECT 7" & _
" FROM Digits UNION ALL SELECT 8 FROM Digits" & _
" UNION ALL SELECT 9 FROM Digits ) AS DT1"

' Create a test table
.Execute _
"CREATE TABLE Test (data_col INTEGER" & _
" NOT NULL); "

' Insert 306 rows using Digits table
.Execute _
"INSERT INTO Test (data_col) SELECT Units.nbr" & _
" + Tens.nbr + Hundreds.nbr AS data_col FROM" & _
" (SELECT nbr FROM Digits) AS Units, (SELECT" & _
" nbr * 10 AS nbr FROM Digits) AS Tens, (SELECT" & _
" nbr * 100 AS nbr FROM Digits) AS Hundreds" & _
" WHERE Units.nbr + Tens.nbr + Hundreds.nbr" & _
" BETWEEN 1 AND 306;"

' Create test proc with DECIMAL param
.Execute _
"CREATE PROCEDURE TestProc (" & _
" arg_percent DECIMAL(25, 24) = 1) AS" & _
" SELECT COUNT(*) AS row_count" & _
" FROM (SELECT T1.data_col FROM Test AS T1" & _
" WHERE (SELECT COUNT(*) * arg_percent FROM" & _
" Test AS T3) >= (SELECT COUNT(*) FROM Test" & _
" AS T2 WHERE T1.data_col >= T2.data_col))" & _
" AS DT1;"

' Invoke proc
Dim rs
Set rs = .Execute( _
"EXEC TestProc 0.333333333333333333333333;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--