Solved

SQL, how can I retrieve top 20 hits?

Posted on 2003-11-20
2
194 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Auto Complete Using JQuery 5 93
website file permissions 4 84
jQuery Date picker not working 16 225
Animated .jpg? 13 104
Article by: Matthew
I am a very big proponent of technology compliance standards and strive to meet such criteria in all of my work. That includes my site, which is 100% XHTML 1.0 compliant as determined by the World Wide Web Consortium. https://www.matthewstevenkel…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
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).

726 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