>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.
Correct. SELECT TOP <nn> ... is equivalent to SELECT ... LIMIT <nn> The <nn> rows returned are the first <nn> rows of the completed, grouped, computed, and ordered result-set ... that is SELECT TOP <nn> ... is the same as SELECT TOP <nn> * FROM (SELECT ...) and SELECT ... LIMIT <nn> is the same as SELECT * FROM (SELECT ...) LIMIT <nn> > > tenholde > >-----Original Message----- >From: [email protected] [mailto:sqlite-users- >[email protected]] On Behalf Of James K. Lowden >Sent: Sunday, February 09, 2014 6:04 PM >To: [email protected] >Subject: Re: [sqlite] FW: Need Help with Golf Handicap Calculation > >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 >_______________________________________________ >sqlite-users mailing list >[email protected] >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
