ArraySort() Issues

[GudTimz]

I am trying to sort some data from a query.

Here is the code:

<CFQUERY name="tope">
SELECT recipient,reccity,recstate

FROM orders

ORDER BY recipient
</CFQUERY>

<CFSET topearray = ArrayNew(2)>
<CFOUTPUT query="tope" group="recipient">
<CFSET ordercount = 0>
<CFOUTPUT>
<CFSET ordercount = ordercount + 1>
</CFOUTPUT>
<CFSET topearray[tope.currentrow][1] = "#ordercount#">
<CFSET topearray[tope.currentrow][2] = "#recipient#">
<CFSET topearray[tope.currentrow][3] = "#reccity#">
<CFSET topearray[tope.currentrow][4] = "#recstate#">
</CFOUTPUT>

<CFSET temp = ArraySort(topearray,"numeric")>

<CFOUTPUT>
<CFDUMP var="#topearray#">
</CFOUTPUT>

The Data from query have several orders from several recipients. Some receipients have ordered twice or more. So I grouped those recipients together in a loop and set some simple variables to calculate how many orders have been placed for each recipient.

This part works great!

I now need to sort this data to determine which recipients have ordered the most. I thought an array would be teh best solution, but can not get the ArraySort() to work for me.

I am specifically looking for the top 10 recipients that have placed the most orders.

When I dump my array prior to adding the ArraySort() command, it would bring back the correct data but I would find several "missing array elements" in the dump. What are these and how can I get rid of them?

Secondly, I need to sort the array by the first column of the array so that when I output the loop from the array I can specificy records 1 thru 10 to get my top 10 most active people ordering. It gives me errors everytime. The most specific error had to do with using a struct variable with an array. I wasn't even using Struct() commands. It would also tell me the data its trying to sort is not numeric. I know this, because it is trying to sort a non-numeric column - I do not know how to specifiy which column it sorts by.

Any help?

[-__cfSearching__-]

I only skimmed your post, but from what you described you should be able to do this with just sql.  Add a COUNT(*) to get the number of orders.  Then order the results by count(*) DESCending.  Finally grab the top 10 records using TOP or LIMIT.  The exact syntax is database depedent.

SELECT recipient,reccity,recstate, COUNT(*) AS NumOfOrders

FROM orders

GROUP BY recipient,reccity,recstate

ORDER BY ....

GudTimz wrote:

I am specifically looking for the top 10 recipients that have placed the most orders.

[GudTimz]

Your SQL suggestion has definetely worked for a couple of the results we are looking for, but still does not solve the other data result.

We have to assign points based on show placement for each order in order to compile a list of top winning recipients.

We are going to have to use an array imsure to compile this data on the fly and then sort the data.

Your suggestion has cured a couple problems, but we still have the ArraySort() function not working for us to deal with.

Anyone have any suggestions on how we can sort this data in the array we have and eliminate "missing array elements"?

Thanks.

Chuck

[-__cfSearching__-]

We have to assign points based on show placement for each order in order to compile a list of top winning recipients.

...
We are going to have to use an array imsure to compile this data on the fly and then sort the data.

As mentioned in the last response, you may be over complicating this.  Can you explain how you assign points and why you cannot use a query? A query, or QoQ,  can both calculate the total orders and sort the data far more simply than with a multi-dimensional array.

ArraySort works with single dimensional arrays that contain simple values like [ 8, 3, 4 ].  It does not work with multi-dimensional arrays, because the values are complex objects.  So it cannot be sorted with ArraySort.

yourArray[1] = another array / complex object

yourArray[2] = another array  / complex object

....

[Dan_Bracuk]

Your empty array elements are caused by null values or empty strings in your query results.

You seem to be doing it the hard way.  To find the top 10 recipients, you can do the select count() suggested earlier, but, if you really need the raw data for other things, do it in a Query of Queries.  You'll have to do a bit of work to resolve ties, but you would have had to do that anyway.