I hope this saves someone some time and I hope I'm not way late to the "RANK()" party:
Let's say you have this table: column1 - column2 A - 1 A - 2 A - 2 B - 3 B - 4 B - 4 ...and you need to write SQL to return the most frequently occurring column2 for each distinct column1: A - 2 B - 4 Enter RANK(): SELECT x.column1, x.column2 FROM ( SELECT z.column1, z.column2, COUNT(*) as thecount, RANK() OVER (PARTITION BY z.column1 ORDER BY COUNT(*) DESC) AS therank FROM z GROUP BY z.column1, z.column2 ) AS x WHERE x.therank = 1 Done and done. :-) -- John Bliss - http://www.linkedin.com/in/jbliss ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359108 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
