• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1147
  • Last Modified:

sql server 2008 DENSE_RANK() - OVER (PARTITION

Hi experts
what is the difference between
DENSE_RANK() OVER (PARTITION BY LocationID ORDER BY Quantity  DESC) AS DENSE_RANK,

and
 
DENSE_RANK() OVER (ORDER BY Quantity desc) AS DENSE_RANK,

I understand this
DENSE_RANK() OVER (ORDER BY Quantity desc) AS DENSE_RANK,
0
enrique_aeo
Asked:
enrique_aeo
  • 2
2 Solutions
 
lwadwellCommented:
DENSE_RANK() is a 'windowing' type of aggregate function.  What the PARTITION BY does, is it acts like a 'group by' within the aggregate.  So for a change change in the partition by 'key' - the dense_rank resets to 1 and starts counting again.
Does that make sense?
0
 
lwadwellCommented:
Here is a sample SQL:
with example_data as (
select 'golf' as sport, 'fred' as name, 72 as score union all
select 'golf' as sport, 'jane' as name, 71 as score union all
select 'golf' as sport, 'mary' as name, 75 as score union all
select 'darts' as sport, 'fred' as name, 12 as score union all
select 'darts' as sport, 'jane' as name, 5 as score union all
select 'darts' as sport, 'bill' as name, 15 as score)
select sport, name, score
     , dense_rank() over(order by score) as d1
     , dense_rank() over(partition by sport order by score) as d2
from example_data

Open in new window

Results:
sport	name	score	d1	d2
-----   ----    -----   --      --
darts	jane	5	1	1
darts	fred	12	2	2
darts	bill	15	3	3
golf	jane	71	4	1
golf	fred	72	5	2
golf	mary	75	6	3

Open in new window

Notice how D1 (without partition by) goes from 1 to 6 and D2 goes from 1 to 3 for each sport (the partition by column).
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now