sql-server-2005oracle

Analytical Functions in Sql server 2005


I have a query,which works in ORACLE but doesn't work in SQL SERVER 2005...How can I convert this query to work in SQL SERVER 2005.

select user_name
       , url
       , count(*)
       ,first_value(count(*)) over (partition by user_name 
                                    order by count(*) desc) max_total
from urls
group by user_name, url
order by max_total desc,user_name

Result: alt text


Solution

  • WITH    q AS
            (
            SELECT  user_name, url, COUNT(*) AS cnt
            FROM    urls
            GROUP BY
                    user_name, url
            )
    SELECT  *
    FROM    q qo
    CROSS APPLY
            (
            SELECT  TOP 1 cnt
            FROM    q qi
            WHERE   qi.user_name = qo.user_name
            ORDER BY
                    cnt DESC
            ) qi