distinct but in a specific order?

[scoutt]

ok let me try to explain what I got. I'm trying to do a stats page from a access.log file. I have the ip in a table and was wondering how I can sort them in a way to where when they get listed on the page that they are in order of most visits but don't have any duplicates.

I tried to do a "select DISTINCT ip form stats" but that only gives me 1 of each.

so I thought of doing this

$find = mysql_query("select DISTINCT ip from Stats order by ip");

while($row = mysql_fetch_array($find)){

$find2 = ("select count(ip) from Stats where ip = '".$row["ip"]."'");
$vis = mysql_query($find2);
while ($row2 = mysql_fetch_array($vis)){
$page = $row2["count(ip)"];
}
}

but that will only give the total for that ip and it doesn't nothing to sort them. this works but like UI said it doesn't put them in any order. is there a way to have 1 sql statement so I can get the order I want but only get the distinct so I don't have duplicates.

thanks guys

[WingMan]

I think you need an agregate function Something like ...


Select count(*), IP
from Stats
group by IP
order by count(*)


The group by agregate (spelling?) function will return a distinct list of IP addresses with the number of occurances for each IP

Clear as Mud ??

[scoutt]

it didn't like count(*) at the end. I have this


$find = mysql_query("select count(*), last_visited from Stats GROUP by ip order by count(*)");

[WingMan]

I think you need to group by one of the columns in the select list.

Try it first without the Order by bit, just to see if thats the sort of answer your looking at - I can't remember if you need to alias to count(*) bit :o

[MattR]

SELECT COUNT( * ) AS hits,
ip
FROM stats
GROUP BY ip
ORDER BY hits ASC

[scoutt]

Wingman: if I take the order by off it works just like I had in my first post. in the same order as well.

Matt: I think you are on to something as it is printing them in a order that seems right, but I can seem to get the total count for that ip to print

this doesn't print anything

$page = $row["count(ip)"];

[MattR]

You have to use the alised column:
$page = $row[ 'hits' ]

[scoutt]

ahhh never mind hehe I got it. that was my first time using an alias so I had to read, oh my aching head....

$row["hits"] doe sit all the way.

thanks guys for your guideance and assistance.