Combine two SQL queries
Posted on 2007-11-29
I have two SQL statements that I need to combine into one but I don't know the proper syntax.
The 2 statements are as follows:
SELECT User, Sum(Miles) AS TotalMiles
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
ORDER BY Ranking DESC
The inner select counts records in Query1 where the totalmiles is greater than the current record. i.e. if there are 4 other users that have travelled further, then my ranking is.. 4, add 1 = 5.