This was discussed on my forums a few days ago, but as I rarely see structSort "in the field", I thought I'd share a simple example.
The user had a simple structure where every key was a name and the value was an age. For example:
name | score |
---|---|
bob | 93 |
ted | 90 |
jane | 94 |
al | 91 |
His goal was to display the names in order of their age. structSort can handle this very simply. The structSort argument takes 4 arguments:
- First is the structure you are sorting.
- Second is the sort type. This can be numeric, text (default), or textnocase.
- Third is sortOrder, which is asc or desc (no true or false, ahem, are you listening Transfer?)
- Last is pathToSubElement. That one is a bit scary, so just pretend it doesn't exist for a few minutes.
The result is an array of keys sorted in the right order. So taking the simple desire to sort by age, we can use:
<cfset s = {bob=93,ted=90,jane=94,al=91}>
<cfdump var="#s#">
<cfset sorted = structSort(s, "numeric")>
<cfdump var="#sorted#">

And that's it. Well, you probably don't want to use dump to format your data. Since we have an array of keys as a result, here is how I'd actually display it:
<cfset s = {bob=93,ted=90,jane=94,al=91}>
<cfset sorted = structSort(s, "numeric")>
<cfloop index="name" array="#sorted#">
<cfoutput>#name# is #s[name]# years young.<br/></cfoutput>
</cfloop>
Notice I use the new index/array form of cfloop. If you are on CF7, you would loop from 1 to arrayLen(sorted). The results:
TED is 90 years young.
AL is 91 years young.
BOB is 93 years young.
JANE is 94 years young.
Ok, simple enough, right? What if you want to use that super-special fourth argument I told you was too scary? Well, it really isn't too scary. It comes in handy if you wan to sort structures that aren't as simple as the example above. Consider this data:
<cfset s2 = {}>
<cfset s2.ray = {age=36,rank="General"}>
<cfset s2.scott = {age=42,rank="Private"}>
<cfset s2.todd = {age=29,rank="Major"}>
<cfset s2.fred = {age=51,rank="Corporal"}>
In this example, instead of simple values, every key is itself a structure containing an age and rank value. This is where the fourth argument comes into play. We can now sort by age or rank:
<cfset sortedByAge = structSort(s2, "numeric", "asc", "age")>
<cfdump var="#sortedByAge#" label="sorted by age">
<cfset sortedByRank = structSort(s2, "textnocase", "asc", "rank")>
<cfdump var="#sortedByRank#" label="sorted by rank">
Which outputs:

I hope this helps. And yes - I know the rank sort isn't sorting by the proper military hierarchy. You would have to write custom code to handle that yourself.
Archived Comments
Thanks Ray! This is just what I was looking for. Sadly, coldfusion doesn't seem to have a built-in function to return a sorted structure instead of array.
Structs in CF don't have order.
Good afternoon Raymond,
I'm trying to rank total sales results of salespeople.
Would the structSort be the best way to do so? If so how would I populate the Struct? With a list?
Thanks in advance,
Stephen
Can you share some of your data?
Sure thanks for the quick reply .....
Using MySQL I have this query
<!--- need to get the counts of invoices and dollars for salespersons for two weeks back. --->
<cfquery name="qryTwoWeeksBackSales" datasource="#application.mainDSN#">
SELECT COUNT(*) AS invoice_count, SUM(invoices.invoice_amount) AS invoice_totals, invoices.salesperson_id,payee.payee_last_name
FROM invoices, payee
WHERE payee.payee_id = invoices.salesperson_id
AND invoices.corp_id = #variables.currentCorpId#
AND invoices.customer_id <> 1
AND invoices.invoice_amount > 0.00
AND invoices.invoice_date BETWEEN #CreateODBCDate(variables.twoWeeksBackStartDate)#
AND #CreateODBCDate(variables.twoWeeksBackEndDate)#
GROUP BY invoices.salesperson_id, payee.payee_last_name
ORDER BY payee.payee_last_name, invoices.salesperson_id
</cfquery>
Then I tried this query of queries ..
<cfquery name="qryTwoWeeksBackSalesRank" dbtype="query">
SELECT SUM(invoice_totals) AS rank, payee_last_name, invoice_totals
FROM qryTwoWeeksBackSales
GROUP BY payee_last_name, invoice_totals
ORDER BY invoice_totals DESC
</cfquery>
My goal is to rank the invoice totals by payee last name. In this report we don't want to show the total dollars just the rank of each salesperson (payee).
This QofQ only returns the rank back as total values. So I was headed towards a StructSort to show the 'rank' but can't seem to figure out how to add the data to the Struct?
What I meant was, could you give me an example of the data you want to sort. As I can't run your query, it doesn't really help. Also - this blog entry is about sorting structs, not queries. Do you have a structure actually?
Sorry about that Raymond, I do not have a Structure but want to build one with the query data.
Here is the dump from a sample query, can I put this into a Structure and then output the 'rank'?
As you see below the rank is the same value as the invoice_count.
INVOICE_COUNT, LAST_NAME, RANK, SALESPERSON_ID
60 Bear 60 18027
5 Byran 5 7398
5 Smith 5 8151
83 Marley 83 7948
1 Mbama 1 15560
2 Tender 2 11383
If you want to sort the results by rank, why not use another QofQ and sort it by rank?
Do or do not. There is no try. ......... I hate to say this, but I've tried and tried.
I spent hours yesterday combing the web for the answer. Even if I sort the QofQ by rank it still pull back the same values of that which I'm trying to rank.
In all my searching, your blog entry here is the only hint of completing my task. If you were to create a new blog on Rank using MySQL and CF that would be awesome!
Sorry I was off kilter on your structure blog but, even your first comment was looking for the ability to sort.
CF is awesome, can't believe good info on such a basic function does not exist :)
So to be clear, you tried another QofQ where you did:
select * from qryTwoWeeksBackSalesRank
order by rank desc
and it did NOT work? How did it not work?
Yes I tried the QofQ many different ways. Here is the error using your suggestion.
Query Of Queries runtime error.
The select column reference [rank] is not a column in any of the tables of the FROM table list.
I'm thinking there is no way to quickly rank data from a query in CF?
Searching for rank query data with coldfusion results in a dead end.
I found many frustrated people trying to just show query results in a ranked order, lots of business owners don't want to show actual sales results just who is ahead of whom.
This is why I was thinking to push the query results into a structure and output the rank as in your example above.
Actually this is very possible. Stephen, please send me an email so we can work on this off this blog entry. As it isn't really on topic anymore, I'd like to continue the discussion via email. Note that I'm on the road today so may be very slow to respond. But to be clear - you can absolutely do what you want here - you are just making a small mistake someplace we can fix.