Solved

Include additional fields in an SQL ranking statement

Posted on 2007-12-06
2
347 Views
Last Modified: 2010-05-18
I have an SQL statement that an Experts Exchange expert constructed for me that works perfectly allowing me to output a ranking order listing the ranking number, the user name for that ranking, and the sum of miles for that user.
The results of that query is at http://www.lepirtle.com/lib/milelog/test18.cfm and that code is as follows:
SELECT myqry2.username, myqry2.SumOfmiles, (Select Count(*) from (SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers
INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username)
WHERE [Sumofmiles]>[myqry2].[SumOfmiles])+1 AS Ranking
FROM (SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers
INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username) AS myqry2
ORDER BY myqry2.SumOfmiles DESC;

However,  I would like to add the users town and state to that output but haven't been successful in modifying the SQL statement. The two fields that I want to output are stored in the tbl_mileusers field and are named tbl_mileusers.town, and tbl_miles.st. I have experimented with the following statement:
SELECT myqry2.username, myqry2.SumOfmiles, tbl_mileusers.town, tbl_mileusers.st, (Select Count(*) from (SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers
INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st)
WHERE [Sumofmiles]>[myqry2].[SumOfmiles])+0 AS Ranking
      FROM
            (SELECT tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st, Sum(tbl_miles.miles) AS SumOfmiles
            FROM tbl_mileusers
            INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
            WHERE year(tbl_miles.milesdate) = Year(Date())
            GROUP BY tbl_mileusers.username,tbl_mileusers.town, tbl_mileusers.st) AS myqry2
ORDER BY myqry2.SumOfmiles DESC;
However as you can see from the results at http://www.lepirtle.com/lib/milelog/test19.cfm, the "ranking" number is erroneous.
Can someone please correct my error?
Thanks.

0
Comment
Question by:lepirtle
[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 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20424444
SELECT myqry2.username, myqry2.town, myqry2.st, myqry2.SumOfmiles, (
      Select Count(*) from (
            SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
            FROM tbl_mileusers
            INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
            GROUP BY tbl_mileusers.username) AS X
      WHERE [Sumofmiles]>[myqry2].[SumOfmiles]) +1 AS Ranking
FROM (
      SELECT tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st, Sum(tbl_miles.miles) AS SumOfmiles
      FROM tbl_mileusers
      INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
      GROUP BY tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st) AS myqry2
ORDER BY myqry2.SumOfmiles DESC;

0
 

Author Comment

by:lepirtle
ID: 20424502
You nailed it!!
Thank you so very much.
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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 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