SQL Server: add ranking to nested selects

Question

I am using the following stored procedure which works fine so far.

Can someone tell me how I have to change this in order to get a ranking for each of the nested Selects ? What I am looking for is a new rank that gets added to each of them so that every nested Select starts with 1, 2, 3 etc.

My SP:

SELECT  *
FROM    (
            SELECT 
            (
                SELECT      policy, 
                            COUNT(*) AS count0
                FROM        Log_PE 
                WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -1, GETDATE()), 112) + '01', 112)
                GROUP BY    policy
                ORDER BY    count0 desc, policy
                FOR XML PATH('currentMonth'), ELEMENTS, TYPE
            )
            UNION ALL
            SELECT
            (
                SELECT      policy, 
                            COUNT(*) AS count1
                FROM        Log_PE 
                WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -1, GETDATE()), 112) + '01', 112)
                GROUP BY    policy
                ORDER BY    count1 desc, policy
                FOR XML PATH('prevMonth2'), ELEMENTS, TYPE
            )
            UNION ALL
            SELECT
            (
                SELECT      policy, 
                            COUNT(*) AS count2
                FROM        Log_PE 
                WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -2, GETDATE()), 112) + '01', 112)
                GROUP BY    policy
                ORDER BY    count2 desc, policy
                FOR XML PATH('prevMonth3'), ELEMENTS, TYPE
            )
            UNION ALL
            SELECT
            (
                SELECT      policy, 
                            COUNT(*) AS count3
                FROM        Log_PE 
                WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -3, GETDATE()), 112) + '01', 112)
                GROUP BY    policy
                ORDER BY    count3 desc, policy
                FOR XML PATH('prevMonth4'), ELEMENTS, TYPE
            )
            UNION ALL
            SELECT
            (
                SELECT      policy, 
                            COUNT(*) AS count4
                FROM        Log_PE 
                WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -4, GETDATE()), 112) + '01', 112)
                GROUP BY    policy
                ORDER BY    count4 desc, policy
                FOR XML PATH('prevMonth5'), ELEMENTS, TYPE
            )
            UNION ALL
            SELECT
            (
                SELECT      policy, 
                            COUNT(*) AS count5
                FROM        log_PE 
                WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -5, GETDATE()), 112) + '01', 112)
                GROUP BY    policy
                ORDER BY    count5 desc, policy
                FOR XML PATH('prevMonth5'), ELEMENTS, TYPE
            )
        ) AS Data(XmlData)
    FOR XML PATH(''), ELEMENTS, TYPE

END

Many thanks for any help with this, Tim.

Not quite sure what you are trying to achieve. Can you illustrate some data output pls? — , Apr 01 '14 at 16:14
Are there a known number of select statements? Or do you want this to be independent of the how many there are (i.e. recursive) — SS781, Apr 01 '14 at 16:16

score 1 · Accepted Answer · answered Apr 01 '14 at 16:20

1

Add this column to each of your nested selects:

rank() over(order by count(*) desc, policy) [Rank]

answered Apr 01 '14 at 16:20

Ron Smith

3,241
1
13
16

Thanks. Where do I put that ? Just add it above the xml line for each select ? – user2571510 Apr 01 '14 at 17:51
1

If you want to make it so that "every nested Select starts with 1, 2, 3 etc", I'd put it right before the policy line in each subquery. – Ron Smith Apr 01 '14 at 17:59
One more question on this: How can I order the whole xml string by this new [Rank] ? – user2571510 Apr 01 '14 at 18:14
1

You are quite welcome! Happy to help. :) It should already be ordered by the Rank since the Rank is ordered by count(*) desc,policy, and that is what your sub-queries are ordered by. You could replace your order by clause in each sub-query with `order by [Rank]` and it should have the same effect. – Ron Smith Apr 01 '14 at 18:27
Sorry, I tested with adding ORDER BY [Rank] to the main select as I need this over-all but this creates an error 'Invalid column name'. – user2571510 Apr 01 '14 at 18:43
1

Yeah, that won't work because after you have converted the results to XML, there is no Rank column name. Sorting within each sub-query doesn't work? – Ron Smith Apr 01 '14 at 18:52
Thanks, it is already sorted correctly within the nested Selects, I was just trying to get it sorted over-all. Never mind, I think I found a work-around for this part. – user2571510 Apr 01 '14 at 19:55

SQL Server: add ranking to nested selects

1 Answers1