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:
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