Select distinct values from many tables from a SQL Server query using group by and order by?

I'm trying to select distinct top 10 url, it's count(url) and size from log_table joining other tables too.

I tried the following SQL Server query:

select distinct top 10 
    url, count(url) as hits, 
  开发者_开发问答  size as data 
from log_table 
where log_table.IP in 
             (select IPAddress from IP where IP.IPId in 
                    (select IPId from userIP where userIP.userId in 
                         (select userId from Users)))
group by url, size 
order by hits desc

It doesn't give me distinct url. But when I try the following query without size, it gives distinct url and hits.

select distinct top 10 
   url, count(url) as hits 
from log_table 
where log_table.IP in 
        (select IPAddress from IP where IP.IPId in 
             (select IPId from userIP where userIP.userId in 
                  (select userId from Users)))
group by url 
order by hits desc

What do I need to do for selecting distinct url, it's hits and size used. url, size are from log_table table.

Any help is appreciated.

Thank you.

The reason that your first query isn't returning distinct url's is because you are grouping by both the url and size. When you group by both these columns then you'll get a row for each combination of values. So if url "a" has both size 5 and 10 then two groups will be created, [a 5] and [a 10]. You'll have to use some aggregate function on the size column to get something back, for example:

select top 10 
   url, count(url) as hits 
   , max(size) as size
from log_table 
where log_table.IP in 
        (select IPAddress from IP where IP.IPId in 
             (select IPId from userIP where userIP.userId in 
                  (select userId from Users)))
group by url 
order by hits desc