?
Solved

SQL to show higest values

Posted on 2003-02-24
16
Medium Priority
?
435 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
[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
  • 6
  • 4
  • 2
  • +1
16 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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

762 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