Analytical Functions in Sql server 2005
I have a query,which works in ORACLE but doesn'开发者_C百科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:
Because of my comment is big I will write it as answer:(
@tanging It is not much correct but there is a correct path...Maybe I have solved this problem by queries but I wanted it by analytical functions....My query is this
select urls.user_name
,urls.url
,count(*) ct
,max_amount
from urls
,(select user_name
,max(amount) max_amount
from (select user_name
,url
,count(*) amount
from urls
group by user_name,url) t1
group by user_name) t2
where urls.user_name=t2.user_name
group by urls.user_name,urls.url,max_amount
order by max_amount desc,urls.user_name,ct desc
@tanging This is test data...
create table urls(
user_name varchar2(100),
url varchar2(100)
);
insert into urls
values('mariami','google.com');
insert into urls
values('mariami','google.com');
insert into urls
values('mariami','google.com');
insert into urls
values('giorgi','google.com');
insert into urls
values('giorgi','google.com');
insert into urls
values('giorgi','facebook.com');
insert into urls
values('giorgi','facebook.com');
insert into urls
values('giorgi','facebook.com');
insert into urls
values('giorgi','facebook.com');
insert into urls
values('mariami','facebook.com');
insert into urls
values('a','facebook.com');
The result of my query is:
And your Query's Result is:
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
精彩评论