Solved

SQL, how can I retrieve top 20 hits?

Posted on 2003-11-20
2
191 Views
Last Modified: 2010-04-06
"INSERT INTO statslog (ip,accesstime,thepage,thetype,successCode,thebytes)
      VALUES ('$ip','$access_time','$link[0]','$link[1]','$success_code','$bytes')";

I am doing log anaylsis. I inserted these fields into table.
How can I get TOP 20 Hits URL from thepage field?
How can I get TOP 20 IP addres from ip field?
I am using MySQL..so I can't use select TOP 10...

Thank you for your help.
0
Comment
Question by:horizzang
2 Comments
 
LVL 6

Accepted Solution

by:
DoppyNL earned 50 total points
ID: 9785844
select `somefield`, count(`somefield`) as Count_of_Somefield from `sometable` group by `somefield` order by `Count_of_Somefield` DESC LIMIT 0, 20

`somefield` --> the field you want to see a top for
count(`somefield`) --> also display the number of occurances
group by --> we want the group everything
order by --> the result; highest count at the top; thus desc
limit --> limit the result to 20 records and start at position 0 (the beginning)

change somefield to any field you like and you should get your results :)
0
 
LVL 11

Expert Comment

by:Zontar
ID: 9790061
SELECT thepage,ip,COUNT(ip) AS ipcount FROM statslog GROUP BY ip ORDER BY ip,thepage DESC LIMIT 20;
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now