1

I have a below query :

select id, firstname,LastName,Company, dense_rank() over (partition by company order by id ) 

from UserAdditionalData 

where Company is not null

Here is the result that I am getting: http://prntscr.com/a9d454. Dense_Rank function is not working,please help me in this.

ninesided
  • 23,085
  • 14
  • 83
  • 107
Ashwani Kumar
  • 21
  • 6
  • can you provide some example data that demonstrates the problem? – ninesided Feb 29 '16 at 11:43
  • I have given url above. – Ashwani Kumar Feb 29 '16 at 11:43
  • In what respect is the result not what you expected? – Ed Harper Feb 29 '16 at 11:45
  • @Ed,What I want is that partition by company.But ,If the company is same then last column value remains the same for same company. – Ashwani Kumar Feb 29 '16 at 11:48
  • 1
    It is working - the rows have been partitioned by `company` and within each partition there is a (dense) rank for each row in ascending order of `id` - exactly what the query asked for. If this is not your desired behaviour then you will need to write an appropriate query to ask for that behaviour. – MT0 Feb 29 '16 at 11:49

1 Answers1

3

What I want is that partition by company.But ,If the company is same then last column value remains the same for same company.

That is not partitioning by company - in fact it requires no partitioning at all.

What you want is:

SELECT id,
       firstname,
       LastName,
       Company,
       dense_rank() over ( order by company ) AS Company_Rank
FROM   UserAdditionalData 
WHERE  Company IS NOT NULL
MT0
  • 143,790
  • 11
  • 59
  • 117