Re: maxrecords property doesn't work for me

Bob Barrows [MVP] wrote:
PKI_Dave wrote:
Thanks. I'll give it a try if it results in what I need.
It isn't the same as the five lowest scores
since it will need to disregard scores posted prior to the 10th
oldest.

That's what my suggested query does.

The real
logic required makes this a little difficult. What I want is to find
the 10 most recent records and then sum the lowest 5 scores from
these last 10 records.

Well that can be accomplished by a further subquery

strSQL = "SELECT SUM(q1.CurrentDifferential) as scoretotal " & _
"FROM (
"SELECT TOP 5 q.ScoreDate, q.CurrentDifferential " & _
"FROM (" & _
"SELECT TOP 10 ScoreDate, CurrentDifferential FROM Score " & _
"WHERE PlayerName = '" & strPlayersName & _
"' ORDER BY ScoreDate DESC) As q " & _
"ORDER BY q.ScoreDate ASC) As q1"


Ah. Another problem - not really a typo. It should be:

strSQL = "SELECT SUM(q1.CurrentDifferential) as scoretotal " & _
"FROM (
"SELECT TOP 5 q.ScoreDate, q.CurrentDifferential " & _
"FROM (" & _
"SELECT TOP 10 ScoreDate, CurrentDifferential FROM Score " & _
"WHERE PlayerName = '" & strPlayersName & _
"' ORDER BY ScoreDate DESC) As q " & _
"ORDER BY q.CurrentDifferential ASC) As q1"


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: maxrecords property doesnt work for me
    ... I just implemented your SQL statement and tested it. ... It isn't the same as the five lowest scores ... "ORDER BY q.CurrentDifferential ASC) As q1" ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: maxrecords property doesnt work for me
    ... "ORDER BY q.ScoreDate ASC" ... Of course, I don't have access to your database so I can't test this, ... Microsoft MVP -- ASP/ASP.NET ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: Multihomed DNS Server Mailserver Webserver Fileserver
    ... Kevin D. Goodknecht posted their thoughts, ... Newsgroup Etiquette: ... Please direct all replies to the newsgroup so all can benefit. ... Ace Fekay, MCSE 2000, MCSE+I, MCSA, MCT, MVP ...
    (microsoft.public.win2000.dns)
  • Re: Multihomed DNS Server Mailserver Webserver Fileserver
    ... Kevin D. Goodknecht posted their thoughts, ... Newsgroup Etiquette: ... Please direct all replies to the newsgroup so all can benefit. ... Ace Fekay, MCSE 2000, MCSE+I, MCSA, MCT, MVP ...
    (microsoft.public.windows.server.dns)
  • Re: Decompiler.NET reverse engineers your CLS compliant code
    ... >> newsgroup was intended for. ... But expected from an MVP, ... > continue with your harrasement, I certainly can't stop you, but I ... my thoughts on your posts. ...
    (microsoft.public.dotnet.languages.csharp)