Solved

SQL, how can I retrieve top 20 hits?

Posted on 2003-11-20
2
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

717 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