CODEWITHSUNDEEP
sql
Vivek Chauhan
Vivek Chauhan

Reputation: 266

Partition not giving expected result

I have run the query below and I've gotten this unexpected result. A partition keyword is used to make grouping on the mentioned column and then assign it a unique key in each group but, in my case, prnk only giving 1 and 2 . It's not create a different group for different salary value.

Declare @Temptable table(EID int ,
Salary int,
Ename varchar(80)
)

INSERT INTO @Temptable values (1,500,'Dev')
INSERT INTO @Temptable values (2,1500,'Dev1')
INSERT INTO @Temptable values (3,1700,'Dev2')
INSERT INTO @Temptable values (4,500,'Dev3')
INSERT INTO @Temptable values (5,600,'Dev4')
INSERT INTO @Temptable values (6,900,'Dev5')
INSERT INTO @Temptable values (7,20000,'Dev6')

--select * from @Temptable
SELECT 
        rnk=row_number() over (order by Salary desc),
        prnk=row_number() over (partition by salary  order by salary ),     
        * FROM @Temptable

Upvotes: 0

Views: 120

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If by "different group for each salary value" you mean a ranking of the values, then use rank() or dense_rank() instead of row_number():

SELECT prnk=dense_rank() over (order by salary ),

Upvotes: 2

Glenn
Glenn

Reputation: 9160

If you partition by salary, your data will be broken into 6 groups:

EID  Salary  Ename  row_number

1    500    'Dev'   1
4    500    'Dev3'  2

----

5    600    'Dev4'  1

----

6    900    'Dev5'  1

----

2    1500   'Dev1'  1

----

3    1700   'Dev2'  1

----

7    20000   'Dev6'  1

Then the row_number is applied within each group. So the first group (the "500"s), having only two elements, will have row numbers 1 and 2 applied. The other groups only have one row, so only row number 1 will appear.

Upvotes: 0

Related Questions