RE: SOT: the magic of RANK()

2014-08-14 Thread UXB

>> 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()

2014-08-12 Thread John M Bliss

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()

2014-08-12 Thread Rick Faircloth

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()

2014-08-12 Thread John M Bliss

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