Solved

Query Help - Leaders

Posted on 2013-01-06
7
227 Views
Last Modified: 2013-01-06
Hello,

I have two tables

table 1: cms_members
id|username|public|challenge

table 2: cms_activities
date|user_id|miles|trail

I need to run a query where I can select the top 10 people and display them by rank.


ie

Leaders
username - unique trails - total days - total miles

I started with this but after staring at it for 30 minutes I thought I should ask for help.

SELECT
	cms_members.id,
	cms_members.username
FROM
	cms_members
WHERE
	challenge = 'ex'
AND public = 'Yes'

Open in new window


I am completely lost and would greatly appreciate any help.

Matt
0
Comment
Question by:movieprodw
  • 4
  • 3
7 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38748889
Hi prod,

the method of choosing the "top 10" will vary according to which SQL engine you're running, but the entire list can be chosen with:

SELECT * 
FROM cms_members
INNER JOIN cms_activites
  ON cms_members.id = cms_activites.user_id
ORDER BY miles desc;

Open in new window


Which database are you using?

Kent
0
 
LVL 1

Author Comment

by:movieprodw
ID: 38748898
I am using mysql
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38748914
Easy enough.  :)  LIMIT 10

SELECT * 
FROM cms_members
INNER JOIN cms_activites
  ON cms_members.id = cms_activites.user_id
ORDER BY miles desc
LIMIT 10;

Open in new window



Good Luck,
Kent
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:movieprodw
ID: 38748929
Hello Kdo,

Thank you for the help.

The issue is that it is not totaling the miles per user and 'ranking' them.

I hope I am explaining it correctly.

table 2: cms_activities
date|user_id|miles|trail
01/02/14|23|50|trail2
01/02/16|23|5|trail1
01/02/17|25|50|trail4
01/02/13|25|5|trail1
01/02/15|23|5|trail6
01/02/16|23|50|trail1
01/02/17|25|5|trail7
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 38748936
Sorry.  That query selects the longest trail, not the longest sum.

The simplest query just sums the data in the cms_activities table:

SELECT user_id, sum (miles) totalmiles
FROM cms_activities
GROUP BY user_id
ORDER BY 2
LIMIT 10;

Open in new window


From there you can add columns from either of the joined tables.

SELECT username, sum (miles) totalmiles 
FROM cms_members
INNER JOIN cms_activites
  ON cms_members.id = cms_activites.user_id
GROUP BY username
ORDER BY 2 desc
LIMIT 10;

Open in new window

                                           

The ORDER BY clause allows either column name or column number.  Some SQL engines allow the aliased name in the filter/control clauses, others do not.  But this form should be accepted by all SQL.


Kent
0
 
LVL 1

Author Comment

by:movieprodw
ID: 38748963
Thank you!
0
 
LVL 1

Author Closing Comment

by:movieprodw
ID: 38748964
Perfect
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
The viewer will learn how to count occurrences of each item in an array.

828 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