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
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" 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
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
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
