Solved

SQL, how can I retrieve top 20 hits?

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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
PHP parse error 7 61
Create 4 <div> below each other in php 2 69
Create and populate iFrame onClick of a button? 1 89
Questions about this React unit test 4 66
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…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to dynamically set the form action using jQuery.
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…

829 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