?
Solved

SQL to show higest values

Posted on 2003-02-24
16
Medium Priority
?
438 Views
Last Modified: 2007-12-19
Hi,
I have created a MySQL db for collecting data from @600 wireless APs.
(mainly no. of nodes, in traffic, out traffic)

Table description.
-------------------------------------------------
id   ipaddr  Intraff  Outtraff  Nodes  TimeStamp
int  varchar   int      int      int   timestamp
-------------------------------------------------
I poll the APs every 20 min for the 3 values and insert it into the db.

Therefor I have [((24hrs x 60min)/20)x600]= 43200 records/day

How do I write a SQL query to list the top 10 or 20 APs with respect to Nodes and Outbound traffic.
I AP's Ipaddress can repeat only if the timstamp is more than 1 hr.

Is this posible with SQL ?
or do I have to do it with Perl code ?


(The aim is to find the highly used AP and re-engineer
the wireless access area. Suggesstions to do it in a better manner are welcome.)







0
Comment
Question by:amitubale
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 15

Expert Comment

by:VGR
ID: 8011355
SELECT * FROM yourtable ORDER BY Outtraff DESC, Nodes DESC LIMIT 20;
0
 
LVL 1

Author Comment

by:amitubale
ID: 8011594
I tired the above query.
Since I insert the values every 20 min,
some of the values are repeated. I need to list the values
on per day ( every 24 hrs) basis. I am not sure how to include the timestamp values in the above query to do that.
0
 
LVL 15

Expert Comment

by:VGR
ID: 8011702
I'm not sure to have understood your problem CLEARLY stated as it is :D

anyway, your solution involves something like the above, eventually with UNIQUE to get rid of "repeated" values (you did not mention which fields got repeated, and why), eventually with GROUP BY clauses, and with WHERE Timestamp>=concat(cur_date(),"00:00:00")

HTH
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 7

Expert Comment

by:Big_Red_Dog
ID: 8014198
Use SELECT DISTINCT to get rid of duplicates
0
 
LVL 15

Expert Comment

by:VGR
ID: 8015228
yes, read "DISTINCT" i.s.o. "UNIQUE" in my comment above ;-)
0
 
LVL 1

Author Comment

by:amitubale
ID: 8017516
Hi VGR,
Thank you for the above replies.
I apologize for not explaingin it clearly.

Since there are 600 Aps and values are collected at 20 min interval.
The Ip address( of the Aps)with the max Outtraff and nodes, are repated with the above query.

I need to just single out the top performing Ap's(Ipaddr) every 24 hrs.

thanks
0
 
LVL 1

Author Comment

by:amitubale
ID: 8017520
Hi VGR,
Thank you for the above replies.
I apologize for not explaingin it clearly.

Since there are 600 Aps and values are collected at 20 min interval.
The Ip address( of the Aps)with the max Outtraff and nodes, are repated with the above query.

I need to just single out the top performing Ap's(Ipaddr) every 24 hrs.

thanks
0
 
LVL 15

Expert Comment

by:VGR
ID: 8019069
hummm sample data ?
0
 

Expert Comment

by:fbordin
ID: 8025833
I am not sure to have understood neither but if the problem is that you don't want to get in the second select (24 hours after the 1st) the result of the first one, then I think you have to add a column to your table like "Status" that would be equal 0 at the begining and each time you launch a query, you update the status of the row you get in results to 1, and you exclude from your query the records with a status=1

Am i Clear ?????
0
 
LVL 1

Author Comment

by:amitubale
ID: 8026808
Hi fbordin
I have tired to put the optput f the table desc and some data from mysql prompt to better expain my problem.
here it is
http://homepages.wmich.edu/~s8ubale/index.html

I am new to databases and not so fluent with SQL statments so please explain the implementation of the "Status".
I can add the row to the table but how would it update it.

I would first like to get the unique hosts with maximum number of hosts and then try to would with timestamp to filter out the ones with in 24 hrs.

Please give your suggestions.
thanks
amit.ubale@wmich.edu
0
 
LVL 1

Author Comment

by:amitubale
ID: 8027390
Hi fbordin
I have tired to put the optput f the table desc and some data from mysql prompt to better expain my problem.
here it is
http://homepages.wmich.edu/~s8ubale/index.html

I am new to databases and not so fluent with SQL statments so please explain the implementation of the "Status".
I can add the row to the table but how would it update it.

I would first like to get the unique hosts with maximum number of hosts and then try to would with timestamp to filter out the ones with in 24 hrs.

Please give your suggestions.
thanks
amit.ubale@wmich.edu
0
 
LVL 1

Author Comment

by:amitubale
ID: 8028767
Hi fbordin
I have tired to put the optput f the table desc and some data from mysql prompt to better expain my problem.
here it is
http://homepages.wmich.edu/~s8ubale/index.html

I am new to databases and not so fluent with SQL statments so please explain the implementation of the "Status".
I can add the row to the table but how would it update it.

I would first like to get the unique hosts with maximum number of hosts and then try to would with timestamp to filter out the ones with in 24 hrs.

Please give your suggestions.
thanks
amit.ubale@wmich.edu
0
 

Accepted Solution

by:
fbordin earned 200 total points
ID: 8031854
Try this :

To update the "status" You would have to do it in 2 queries.

First do your select :
SELECT * FROM yourtable ORDER BY Outtraff DESC, Nodes DESC LIMIT 20 WHERE STATUS=0

and  then update the result to 1 :

UPDATE * from yourtable SET STATUS=1 where (SELECT * FROM yourtable ORDER BY Outtraff DESC, Nodes DESC LIMIT 20 WHERE STATUS=0);
0
 
LVL 7

Expert Comment

by:Big_Red_Dog
ID: 8547254
Did any of this help you?  If so, please award a grade to close this question.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

615 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