Many applications these days have some sort of standings table where you can see how you compare to other site members. Forums have them to show top posters, wikis have them to show top contributors, social networking sites have them to show who has the most connections. Google "SQL ranking" and you will get a long list of articles about how to build one of these tables using the database.

One common approach is the following query, which uses a self-join:

view plain print about
1SELECT, a.sales, COUNT(a.sales) AS rank
2FROM sales a, sales b
3WHERE a.sales < b.sales OR (a.sales = b.sales AND =
4GROUP BY, a.sales
5ORDER BY rank

In the interest of Friday fun, I decided to take a different route and completely remove the database from the equation. I used query of queries to create the recordset for a standings table for the Sky Blue Pink Pencil Club. Members in SBPPC collect rare pencils that are colored sky blue pink and are very proud of their collections. But who are the top pencil collectors?

Let's start by building a recordset of names and the size of their pencil collection:

view plain print about
2/* Create a recordset containing each member of the SBPPC club and the size of their pencil collection. */
3names = listToArray("Jason,Jennifer,Sophia,Gary,Sue,Greg");
4pencils = listToArray("10,15,20,40,50,20");
6totals = queryNew("");
7queryAddColumn(totals, "name", names);
8queryAddColumn(totals, "pencils", pencils);
10/* Make a copy of the query for use in the QoQ below. */
11copy = duplicate(totals);

Note the last line within the cfscript tags where we create a copy of the original query. Query of queries does not support table aliases, only column aliases. It does, however, support joining two query recordsets together, which is the behavior we want.

Now let's build the standings query. To determine rank in SQL, we do a self-join on the data and count the number of records ahead of the current record when listed in order.

view plain print about
1<cfquery name="standings" dbtype="query">
2    SELECT, totals.pencils, COUNT(totals.pencils) AS [position]
3    FROM totals, copy
4    WHERE totals.pencils < copy.pencils
5        OR ( = AND totals.pencils = copy.pencils)
6    GROUP BY, totals.pencils
7    ORDER BY [position]

When output, we see that Sue leads the pack, Gary comes in second, and Greg and Sophia are tied for third.

Sue	50	1
Gary	40	2
Greg	20	3
Sophia	20	3
Jenn	15	5
Jason	10	6