Rank():
This function will assign the rank number to each record present in a
partition.
If the function in SQL encounters two equal values in the same partition,
it
will assign the same rank number to both values and skips the next number
in
the ranking for successive partitions.
Dense_rank():
This
function assigns rank number similar to the above function ,
but
the ranks are consecutive for successive partitions . No ranks are skipped
if
there are ranks with multiple items.
|
SYNTAX
& EXAMPLE
Rank() over (Partition by <columns>
Order by <Columns>)
sel a.* ,rank() over (partition by pol_mbr_id order by end_dt desc)
from edw_base.enrl_elct_d a where pol_mbr_id = '11,024,736'
Dense_rank() over (Partition by <columns> Order by
<Columns>)
sel a.*,dense_rank()over(partition by pol_mbr_id order by end_dt desc) from edw_base.enrl_elct_d a where pol_mbr_id = '11,024,736' |
|
|
