find the top 8 values in recordset, done in code 
Author Message
 find the top 8 values in recordset, done in code

What would be the best way to write code to find the top  8 values in a
recordset. I  am currently using a Top 8 query but it is not returning the
correct values. The top query does not distinguish between matching values,
and therefore skews the results. I guess I need to rank the values?...

the objective is to find the top speed of a 16 km
h range where 85% of vehicules are moving. You do this by finding the 8
largest volumes of vehicules and returning the top most value of that speed
range ex: 80 to 30 km/h, top range is 80.

Does anyone have code to do this?

--

Thanks,

Stephane Viau
Ottawa, Canada



Sat, 19 May 2001 03:00:00 GMT  
 find the top 8 values in recordset, done in code
If you select the top 8 that does mean the first till 8th record that
does even match. If you want to get the top 8 rankings you need to
select top 8 ... from ... order by ... DESC;

Hope thats waht youre looking for

Quote:

> What would be the best way to write code to find the top  8 values in a
> recordset. I  am currently using a Top 8 query but it is not returning the
> correct values. The top query does not distinguish between matching values,
> and therefore skews the results. I guess I need to rank the values?...

> the objective is to find the top speed of a 16 km
> h range where 85% of vehicules are moving. You do this by finding the 8
> largest volumes of vehicules and returning the top most value of that speed
> range ex: 80 to 30 km/h, top range is 80.

> Does anyone have code to do this?

> --

> Thanks,

> Stephane Viau
> Ottawa, Canada

--
Ralf van Gelder

Ford Motor Company
To Reply: replace the "x" with a "r"



Sun, 20 May 2001 03:00:00 GMT  
 find the top 8 values in recordset, done in code
Stephane --
    Let's make sure I understand your task.  You are looking for 16 kmh
ranges (0-15,16-31,32-47,...); you want the top of the range which is higher
than the speed of 85% of the vehicles.  I don't see how your "8 largest
volumes of vehicles" algorithm can gurantee that.  Here's a set of queries
that should work (but untested).
    First create a summary table containing counts by 16 kmh range
        SELECT 16*Int(speed/16)+15 as range, count(*) as nveh
        INTO sumtab
        FROM <source>
        GROUP BY 16*int(speed/16)+15
    Then create a totals table so we know how many vehicles there are
        SELECT sum(nveh) as tveh
        INTO tottab
        FROM sumtab
    Then create a cums table so we know how many vehicles are going at a
range or slower
        SELECT s1.range,sum(s2.nveh) as nveh
        INTO cumtab
        FROM sumtab as s1 JOIN sumtab as s2 on s1.range>=s2.range
        GROUP BY s1.range
    Then select the lowest range containing 85% of the vehicles
        SELECT min(cumtab.range)
        FROM cumtab JOIN tottab on cumtab.nveh<=tottab.tveh
        WHERE cumtab.nveh>=.85*tottab.tveh
HTH, Roger
Quote:

>What would be the best way to write code to find the top  8 values in a
>recordset. I  am currently using a Top 8 query but it is not returning the
>correct values. The top query does not distinguish between matching values,
>and therefore skews the results. I guess I need to rank the values?...

>the objective is to find the top speed of a 16 km
>h range where 85% of vehicules are moving. You do this by finding the 8
>largest volumes of vehicules and returning the top most value of that speed
>range ex: 80 to 30 km/h, top range is 80.

>Does anyone have code to do this?

>--

>Thanks,

>Stephane Viau
>Ottawa, Canada



Sun, 20 May 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

2. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

3. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

4. Finding the number value of a hex value

5. Updating RecordSet with values of a second RecordSet

6. How to set Constant Values to Control Code Values

7. Recordset: What am I doing wrong?

8. setting file.value -- can it be done?

9. Recordset object, what am i doing wrong?

10. Cannot access fields using recordset property when doing a self-join

11. ADO Update the Database with the modify done on a disconnected recordset in background mode

12. doing a find of the whole drive for a set of files

 

 
Powered by phpBB® Forum Software