RE: SOT: the magic of RANK()
18 years in and I just learned about it today Old dogs... new tricks... who says it can't happen? :) I know the feeling. Dennis Powers UXB Internet - A website Design and Hosting Company P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 W: http://www.uxbinternet.com W: http://www.ctbusinesslist.com ~| 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:359116 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SOT: the magic of RANK()
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
Re: SOT: the magic of RANK()
Nice! I've never heard of Rank()... On 8/12/2014 4:21 PM, John M Bliss wrote: 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. :-) ~| 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:359113 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SOT: the magic of RANK()
Happy to not be the only one. 18 years in and I just learned about it today (IIRC). :-) On Aug 12, 2014 8:49 PM, Rick Faircloth r...@whitestonemedia.com wrote: Nice! I've never heard of Rank()... On 8/12/2014 4:21 PM, John M Bliss wrote: 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. :-) ~| 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:359114 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
