How to add row number column base on duplicated records that currently ordered

Question

Hi,

I want to insert a page_number in a record that continuously count base on duplicate in a column. example output:

ID      PAGE_ORDER  PAGE_NUMBER
1          7            1
2           7            1
3           7            1
4           7            1
5           7            1
6           10           2
7           10           2
8           10           2
9           10           2
10         10           2
11         13           3
12         13           3
13         13           3
14         13           3
15         13           3
16         9            4
17         9            4
18         9            4
19         9            4
20         9            4
21         16           5
22         16           5
23         16           5
24         16           5
25         16           5
26         11           6
27         11           6
28         11           6
29         11           6
30         11           6
31         12           7
32         12           7
33         12           7
34         12           7
35         12           7
36         4            8
37         4            8
38         4            8
39         4            8
40         4            8
41         5            9
42         5            9
43         5            9
44         5            9
45         5            9
46         14           10
47         14           10
48         14           10
49         14           10
50         14           10
51         15           11
52         15           11
53         15           11
54         15           11
55         15           11
56         6            12
57         6            12
58         6            12
59         6            12
60         6            12
61         1            13
62         1            13
63         1            13
64         1            13
65         1            13
66         2            14
67         2            14
68         2            14
69         2            14
70         2            14
71         8            15
72         8            15
73         8            15
74         8            15
75         8            15
76         3            16
77         3            16
78         3            16
79           3          16
80           3          16

How can I add the page_number column just like in the output? The rule of page_number column is to count continuously base on duplicate records in page_order column. Please advice. Thank you.

Answer 1

I'm not really sure how your data is ordered - there seems to be lots of duplicates in no particular order.

However, if you're on SQL Server 2005 or 2008, your solution will probably involve RANK

http://msdn.microsoft.com/en-us/library/ms176102.aspx

Answer 2

I'm a bit unclear on what to do with the ID column since it's always the same value, but this should give you the output you want given the data you provided:

WITH Results AS (
    SELECT 
        MAX(ID) as ID, 
        Page_Order, 
        ROW_NUMBER() OVER (ORDER BY Page_Order DESC) as Page_Number 
    FROM 
        PageNumberTable
    GROUP BY 
        Page_Order

)
SELECT 
    Results.* 
FROM 
    PageNumberTable 
    JOIN Results ON PageNumberTable.Page_Order = Results.Page_Order

Answer 3

Try this

SELECT id, page_order,
       DENSE_RANK() OVER(ORDER BY 
             (MIN(id) OVER(PARTITION BY page_order)) as page_number
FROM table_name
ORDER BY id

edit: try this instead

;WITH T1 AS 
(
  SELECT id, page_order, MIN(id) OVER(PARTITION BY page_order) AS min_id
)
SELECT id, page_order, DENSE_RANK() OVER(ORDER BY min_id) AS page_number
FROM T1
ORDER BY id

Answer 4

I found the solution in my problem:

select *, dense_rank() over (order by sort) 
from (select *, sort = min(ID) over (partition by page_order) 
        from #tbl) c 
order by c.ID 

Thanks to KH Tan in msdn forums. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/55a18861-50a1-4f8a-b6f7-16992fa51e94

Thank you Guys!