CODEWITHSUNDEEP
sqlsql-server
H.T
H.T

Reputation: 21

divide data in sql to groups order by another column

I have this set of data 

shopId companyId   date
1        1         25/8/2015
2        1         26/8/2015
3        1         22/8/2015
4        2         20/8/2015
5        2         27/8/2015

what i need is to get this result

shopId companyId   date        dense_rank
1        2         27/8/2015       1
2        2         20/8/2015       1
3        1         26/8/2015       2
4        1         25/8/2015       2
5        1         22/8/2015       2

how to get all groups ranked but order with date

Upvotes: 0

Views: 42

Answers (2)

M.Ali
M.Ali

Reputation: 69554

SELECT * 
      , DENSE_RANK() OVER (ORDER BY companyId DESC, [Date] DESC) AS [DENSE_RANK]
FROM TableName

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

If you want the groups ordered by date, then you need two steps: first get the maximum date for each group. Then use dense_rank():

select shopid, companyid, date,
       dense_rank() over (order by maxd desc) as dense_rank
from (select t.*, max(date) over (partition by companyid) as maxd
      from table t
     ) t

Note: this assumes that your date is really stored as a date and not as a string. You will need additional transformations if the data is (improperly) stored as a string.

Upvotes: 0

Related Questions