Geek Answers Handbook

Rating by sql server groups

The problem seems simple, but I can not get around it, this is for sql server

 what I have in a table :            What I need as a output .
 cksum          id                      cksum       id
-2162514679     204                    -2162514679  204    1
-2162514679     207                    -2162514679  207    1
-2162514679     215                    -2162514679  215    1
-2162514679     218                    -2162514679  218    1
-2162514679     221                    -2162514679  221    1
-2160286363     257                    -2160286363  257    2
-2160286363     260                    -2160286363  260    2
-2160286363     332                    -2160286363  332    2
-2162514679     335                    -2162514679  335    3
-2162514679     338                    -2162514679  338    3
-2126731931     348                    -2126731931  348    4
-2126731931     387                    -2126731931  387    4

The table is sorted by id, I need a rank following the id column, but it groups cksum, notices that cksum can go back to the previous value, but still has its own rank due to ID (this is the case for the value 2162514679, it appears 5 times at the beginning and a second time stronger, and they make up two different categories). I have been doing this for several hours, and it seems like it is really stupid, like using row_number with a partition or using CTE, but nope .... cannot find the logic of logic for this ... who has an answer?

+4
2

. - . , , . , :

select cksum, id, dense_rank() over (order by minid)
from (select t.*, min(id) over (partition by cksum, grp) as minid
      from (select t.*,
                   (row_number() over (order by id) -
                    row_number() over (partition by cksum order by id)
                   ) as grp
            from table t
           ) t
     ) t;
+3

, LAG, SQL Server 2008 R2:

;WITH CTE_RN AS (
   SELECT cksum, id, ROW_NUMBER() OVER(ORDER BY id) AS rn
   FROM Checksums
), CTE_LAG AS (
   SELECT c1.cksum, c1.id, c1.rn,
          (CASE WHEN c2.cksum IS NULL OR c1.cksum = c2.cksum THEN 0
                ELSE 1
           END) AS flag 
   FROM CTE_RN AS c1
   LEFT JOIN CTE_RN AS c2 ON c1.rn = c2.rn+1
)
SELECT cksum, id,  (SELECT SUM(flag)
                    FROM CTE_LAG AS t2        
                    WHERE t2.rn <= t1.rn) + 1 AS [rank]     
FROM CTE_LAG AS t1

CTE_LAG ( OP):

cksum       id  rn  flag
-------------------------
-2162514679 204 1   0
-2162514679 207 2   0
-2162514679 215 3   0
-2162514679 218 4   0
-2162514679 221 5   0
-2160286363 257 6   1
-2160286363 260 7   0
-2160286363 332 8   0
-2162514679 335 9   1
-2162514679 338 10  0
-2126731931 348 11  1
-2126731931 387 12  0

flag 1, cksum cksum, flag 0.

rank - flag.

+1

More articles:


All Articles