CODEWITHSUNDEEP
sql-server-2005oracle-database
kupa
kupa

Reputation: 1890

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

Upvotes: 2

Views: 1053

Answers (3)

kupa
kupa

Reputation: 1890

@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: alt text

And your Query's Result is: alt text

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425251

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

Upvotes: 0

kupa
kupa

Reputation: 1890

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

Upvotes: 2

Related Questions