Thanks for the reply. Perhaps my misunderstanding of the relation between
rank and the windowing function is wrong.
What I want to achieve for the following is : For a given customer id,
sort his orders. I thought the below would work.
SELECT eh.cmsorderid, eh.orderdate, RANK(orderdate) w FROM order_data eh
window w as (partition by cmscustid order by orderdate);
The rank function instead returns the rank of the order date over all all
order dates.
Example snippet from above
Actual :
6758783 27APR2012 94
6758783 23JUN2012 95
6758785 14DEC2012 96
6758795 18DEC2011 97
6758796 06MAY2012 98
6758798 24MAR2013 99
6758799 23NOV2012 100
Expected :
6758783 27APR2012 1
6758783 23JUN2012 2
6758785 14DEC2012 1
6758795 18DEC2011 1
6758796 06MAY2012 1
6758798 24MAR2013 1
6758799 23NOV2012 1
-b
On Wed, Jul 24, 2013 at 3:17 PM, Shahar Glixman <sglix...@outbrain.com>wrote:
> the argument to rank is simply some value, whereas the rank function
> compare this value
> to the previous value received, if value is same, rank returns ++index,
> otherwise, rank return 1.
> pseudo code:
>
> class Rank {
> int index;
> Object previousValue = null;
> int evaluate(Object value) {
> if (value == previousValue) {
> return ++index;
> }
> previousValue = value;
> index = 1;
> return 1;
> }
>
>
> On Wed, Jul 24, 2013 at 9:59 PM, j.barrett Strausser <
> j.barrett.straus...@gmail.com> wrote:
>
>> It seems as though I am required to pass in an argument to RANK().
>>
>> What is the effect of passing this argument in ?
>>
>> In a RANK function the output for RANK should be the number of rows
>> preceding
>> a row in a given window. As windows are specified by the partition and
>> order by fields I don't understand the effect of passing in an arg to RANK
>>
>> Are the other non-aggregate function similar? Meaning : Cume_dist, Ntile
>> --
>>
>>
>> https://github.com/bearrito
>> @deepbearrito
>>
>
>
> The above terms reflect a potential business arrangement, are provided solely
> as a basis for further discussion, and are not intended to be and do not
> constitute a legally binding obligation. No legally binding obligations will
> be created, implied, or inferred until an agreement in final form is executed
> in writing by all parties involved.
>
> This email and any attachments hereto may be confidential or privileged.
> If you received this communication by mistake, please don't forward it
> to anyone else, please erase all copies and attachments, and please let
> me know that it has gone to the wrong person. Thanks.
>
--
https://github.com/bearrito
@deepbearrito
