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

Question

Hi

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.

Answer 1

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