top predicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have already create a query in order to estimate "Top Values for a group
of records"the values are numbers . My problem is that when for example i
want to estimate 10% top values i get the same result if for example i have
20 numbers(i get 2 as top value) and if i have 12 numbers i get again 2
numbers .there is any method for this.
Thank you.
 
As far as I know, the Top predicate rounds UP to the next whole number when
using PerCent. I would guess that the decision was based on the logic that
you can't return part of a record, so return the entire record.

I know of no easy way do what you seem to want - round the calculated
percent down or round it to the nearest whole number. I would think it
could be done with a ranking query plus calculated query to identify records
for your current query.. Both would have to be based on your current query
using the order by clause and where clause in your current query. Of course
your current query would NOT use the TOP predicate
 
As far as I know, the Top predicate rounds UP to the next whole number when
using PerCent. I would guess that the decision was based on the logic that
you can't return part of a record, so return the entire record.

I know of no easy way do what you seem to want - round the calculated
percent down or round it to the nearest whole number. I would think it
could be done with a ranking query plus calculated query to identify records
for your current query.. Both would have to be based on your current query
using the order by clause and where clause in your current query. Of course
your current query would NOT use the TOP predicate
Click to expand...

Here is something with which I came up with a good deal of trouble
attempting to solve a problem posed here on the 21th I think.

Query: Top ? percent from group

PARAMETERS [Enter percent as decimal:] Decimal;
SELECT TP.tree_nbr, TP.plot_nbr, TP.tree_diameter, (SELECT Count(* )
FROM Treeplots AS TP1
WHERE TP1.plot_nbr = TP.plot_nbr
AND TP1.tree_diameter < TP.tree_diameter
OR (TP.tree_diameter = TP1.tree_diameter)
AND (TP.tree_nbr <= TP1.tree_nbr)) AS size_rank, (SELECT
Count(T.plot_nbr)
FROM Treeplots AS T
WHERE TP.plot_nbr = T.plot_nbr) AS count_per_plot
FROM Treeplots AS TP
GROUP BY TP.tree_nbr, TP.plot_nbr, TP.tree_diameter
HAVING ((((SELECT Count(* )
FROM Treeplots AS TP1
WHERE TP1.plot_nbr = TP.plot_nbr
AND TP1.tree_diameter > TP.tree_diameter
OR (TP.tree_diameter = TP1.tree_diameter)
AND (TP.tree_nbr >= TP1.tree_nbr))/(SELECT
Count(T.plot_nbr)
FROM Treeplots AS T
WHERE TP.plot_nbr = T.plot_nbr))<=
[Enter percent as decimal:]));

Results using a parameter value of .2 (20 percent):

tree_nbr plot_nbr tree_diameter size_rank count_per_plot
14 1 23.1 12 12
27 3 32.5 11 11

Plot 2 only has 7 rows, so 10% of 7 is a fraction of a row. What is a
fraction of a row any how?
 
John said:
As far as I know, the Top predicate rounds UP to the next whole number when
using PerCent.

I know of no easy way do what you seem to want - round the calculated
percent down or round it to the nearest whole number.
Click to expand...

Considering TOP 5 could be re-written using standard SQL as this:

SELECT T1.data_col
FROM Test6 AS T1
WHERE 5 >= (
SELECT COUNT(*)
FROM Test6 AS T2
WHERE T2.data_col <= T1.data_col
);

then I think a TOP 10% with rounding to the nearest could be this:

SELECT T1.data_col
FROM Test6 AS T1
WHERE (
SELECT COUNT(*) * 10/100
FROM Test6 AS T3) >= (
SELECT COUNT(*)
FROM Test6 AS T2
WHERE T2.data_col <= T1.data_col
);

The idea of using PARAMETERS is a good one and something that can't be
achieved with TOP N. Here's a quick demo:

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

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

' Create test data (incrementing integers 1-12)
.Execute _
"INSERT INTO Test6 (data_col) VALUES (1);"
.Execute _
"INSERT INTO Test6 (data_col) SELECT DT1.data_col" & _
" FROM ( SELECT 2 AS data_col FROM Test6" & _
" UNION ALL SELECT 3 FROM Test6 UNION ALL" & _
" SELECT 4 FROM Test6 UNION ALL SELECT 5" & _
" FROM Test6 UNION ALL SELECT 6 FROM Test6" & _
" UNION ALL SELECT 7 FROM Test6 UNION ALL" & _
" SELECT 8 FROM Test6 UNION ALL SELECT 9" & _
" FROM Test6 UNION ALL SELECT 10 FROM Test6" & _
" UNION ALL SELECT 11 FROM Test6 UNION ALL" & _
" SELECT 12 FROM Test6 ) AS DT1;"

' Create procedure
.Execute _
"CREATE PROCEDURE ProcTest6 (arg_percent" & _
" DECIMAL(3, 2) = 1.00) AS SELECT T1.data_col" & _
" FROM Test6 AS T1 WHERE (SELECT COUNT(*)" & _
" * arg_percent FROM Test6 AS T3) >= (SELECT" & _
" COUNT(*) FROM Test6 AS T2 WHERE T2.data_col" & _
" <= T1.data_col);"

' Show results for 'top 10%'
Dim rs
Set rs = .Execute("EXECUTE ProcTest6 0.1")
MsgBox rs.GetString
rs.Close
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
Back
Top