On Sat, 8 Feb 2014 19:47:44 +0000 Ed Tenholder <[email protected]> wrote:
> Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 > FROM (SELECT * FROM (SELECT * FROM (SELECT > ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith" > ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20) > ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10) That's hard to read, so I'm relying on your description, > Logic: > > ? Select the oldest N scores (3 in the example above) > ? From that, select the 20 newest scores > ? From that, select the 10 lowest handicap-indexes: > (Score-Rating)*(113/Slope) ? Return the lowest ScoreDate and the > average of the handicap-indexes multiplied by .96 If you're learning SQL, you'll want to know that in the SQL standard LIMIT does not exist and ORDER BY is valid only to order the final output. I describe ranking rows in a general way at http://www.schemamania.org/sql/#rank.rows. HTH. --jkl _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
