Re: [sqlite] FW: Need Help with Golf Handicap Calculation

2014-02-09 Thread Keith Medcalf 

>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  ... is equivalent to SELECT ... LIMIT 

The  rows returned are the first  rows of the completed, grouped, 
computed, and ordered result-set ... that is

SELECT TOP  ...
is the same as
SELECT TOP  * FROM (SELECT ...)

and

SELECT ... LIMIT 
is the same as
SELECT * FROM (SELECT ...) LIMIT 

>
>   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 +
>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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: Need Help with Golf Handicap Calculation

2014-02-09 Thread Ed Tenholder 
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 +
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

Re: [sqlite] FW: Need Help with Golf Handicap Calculation

2014-02-09 Thread James K. Lowden 
On Sat, 8 Feb 2014 19:47:44 +
Ed Tenholder  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] FW: Need Help with Golf Handicap Calculation

2014-02-08 Thread Ed Tenholder 


From: Ed Tenholder 
Sent: Saturday, February 08, 2014 1:44 PM
To: 'sqlite-users@sqlite.org'
Subject: Need Help with Golf Handicap Calculation

I’m just trying to learn SQL, and after lots of google  searches and reading 
posts on this email list, I’ve gotten pretty close.

Table:  CREATE TABLE Scores (ScoreID Integer Primary Key,ScoreDate 
Text,Player Text,CourseName Text,TeeName Text,Score Integer,Rating Real,Slope 
Integer);


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)

Result:  MAX(ScoreDate)AVG((Score-Rating)*(113.0/Slope))*.96
  2000-05-16 
29.2436825396825



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   



The first SELECT is there because I am going to execute this query iteratively, 
substituting   for the “3”,from 1 to the count of total records   (so I can 
create a chart of the change in handicap over time)


The flaw is that the ScoreDate that is returned is the oldest date in the 
lowest 10 records, and what I need is the oldest date in the most recent 20 
records (from the sub-query).

I cannot figure out how to do this without breaking up the query   using temp 
tables  (which I can do, but I am interested in learning more about SQL and I’m 
sure there must be a way to do this  (if you can solve Soduko puzzles!)

Thanks for any help,

 Ed t.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users