Also, since I'm not interested in world domination (or am I
), I updated the DB to be LonelyCache Wide instead of WorldWide.- Best All Around Board
- LCP_BAR.jpg (50.63 KiB) Viewed 15613 times
, here's the SQL query for the above pic, another small glimpse into the LonelyCache database schema: 
Code: Select all
SELECT Row,Ranking,CacherID,Cacher,Total
,(select sum(OverallPoints)from GeocacherPoints
Where CacherID=r.CacherID and job=1 and RegionID = 1) FindsTotal
,(select sum(OverallPoints)from GeocacherPoints
Where CacherID=r.CacherID and job=2 and RegionID = 1) HidesTotal
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY Total DESC, g.Name) AS Row
,RANK() OVER (ORDER BY Total DESC) AS Ranking
,CacherID, g.Name as Cacher, Total
FROM
(SELECT CacherID, SUM(g.OverallPoints) AS Total
FROM dbo.GeocacherPoints g
WHERE regionid = 1 AND Job IN (1,2)
GROUP BY CacherID) s
JOIN dbo.Geocacher g ON g.ID = CacherID) r
WHERE Row between 1 and 100
ORDER BY Row
