I thought LIMIT did the same thing as TOP. With both LIMIT (or TOP) and ORDER BY, my assumption (and my experience) is that the result is sorted, and then the first NN are returned.
tenholde -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Sunday, February 09, 2014 6:04 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] FW: Need Help with Golf Handicap Calculation On Sat, 8 Feb 2014 19:47:44 +0000 Ed Tenholder <e...@tenholder.net> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
