Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2004
    Posts
    40

    Question Unanswered: Select Last 20 records by Date then Sum Lowest 10 Scores???

    I have a table and am usuing ASP to query the database, the connection is to a MS Access table at the moment but am working to convert to SQL Server.

    Question:

    I need to select the last 20 records, by a date field, then from those 20 records select the 10 lowest scores.

    Example is a member logs on an that member has say 80 total records in the table. Then I need to select the last 20 records entered by the date field then select the lowest 10 scores out of those 20.

    I am new to more compex SQL Statements any help would be mostly appreciated!

    table = HC_ID
    date field = date
    member_id = member
    score = ScoreHC

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT TOP 10 * FROM (
    SELECT TOP 20 * FROM HC_ID ORDER BY [date] DESC) AS XXX
    ORDER BY ScoreHC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    What is the field of the primary key?

  4. #4
    Join Date
    Jan 2004
    Posts
    40
    Each member has an member_ID but we have an auto number field to track individual records in the table

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need it by Member?

    Code:
    SELECT TOP 10 * FROM (
    SELECT TOP 20 * FROM HC_ID 
    WHERE MemberId = @MemberId
    ORDER BY [date] DESC) AS XXX
    ORDER BY ScoreHC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    40
    Originally posted by Brett Kaiser
    SELECT TOP 10 * FROM (
    SELECT TOP 20 * FROM HC_ID ORDER BY [date] DESC) AS XXX
    ORDER BY ScoreHC

    What does the XXX stand for, are you renaming the field that is the Top 10 scores?

  7. #7
    Join Date
    Jan 2004
    Posts
    40

    Talking

    Originally posted by Brett Kaiser
    You need it by Member?

    Code:
    SELECT TOP 10 * FROM (
    SELECT TOP 20 * FROM HC_ID 
    WHERE MemberId = @MemberId
    ORDER BY [date] DESC) AS XXX
    ORDER BY ScoreHC
    Yes by member. From this Top 10 subset I will be summing the scores and them dividing by 10 to get a current average.

    I am going to try and modify as you have above and test.. I appreciate everyones quick response and am very glad I found this db forum today...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's the name of the derived table...you need to give it a name otherwise it'll blow up....

    I'm just doing SELECT *...did you try it?

    Sample DDL and data would help us give you a concrete example...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2004
    Posts
    40
    Looks good so far, I have been frustrated for a few days now trying to figure this out on my own. I found an article on derived table but said nothing about giving it a name.

    Now whats the easiest way to sum the roundHC field of the 10 records

    Here are the data particulars

    table: tbl_HC_Data
    Member ID: MEMBER_ID
    HC Score: RoundHC
    Row ID: HC_ID

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummm..

    Code:
    SELECT TOP 10 SUM(ScoreHC)  FROM (
    SELECT TOP 20 * FROM HC_ID 
    WHERE MemberId = @MemberId
    ORDER BY [date] DESC) AS XXX
    ORDER BY ScoreHC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2004
    Posts
    40
    I tried this:

    strsql4 = "SELECT TOP 10 SUM(RoundHC) AS RoundHCSum FROM (SELECT TOP 20 * FROM tbl_HC_Data WHERE MEMBER_ID= " & Session("HC_MEM_ID") & " ORDER BY [date] DESC) AS XXX ORDER BY RoundHC"


    and got this error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'RoundHC' as part of an aggregate function.

    /HCTracker/Test.asp, line 44


    But when I modified the last of the SQL Statement to this:

    strsql4 = "SELECT TOP 10 SUM(RoundHC) AS RoundHCSum FROM (SELECT TOP 20 * FROM tbl_HC_Data WHERE MEMBER_ID= " & Session("HC_MEM_ID") & " ORDER BY [date] DESC) AS XXX "


    It worked but summed all 20 records not the lowest 10 scores as desired.

    Need the sum of the lowest 10 scores not all 20.

    I used this SQL Statement and it worked fine to display the subset: Which is the population I need.

    strsql4 = "SELECT TOP 10 * FROM (SELECT TOP 20 * FROM tbl_HC_Data WHERE MEMBER_ID= " & Session("HC_MEM_ID") & " ORDER BY [date] DESC) AS XXX ORDER BY RoundHC "

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about (and this would be a lot easier if you posted your ddl and sample data)

    strsql4 = "SELECT SUM(ROUND_HC) AS SUM_ROUND_HC FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM tbl_HC_Data WHERE MEMBER_ID= " & Session("HC_MEM_ID") & " ORDER BY [date] DESC) AS XXX ORDER BY RoundHC) AS YYY "
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2004
    Posts
    40
    sorry for being a newbie but ddl?

    and sample data? I thought that was just the field names you needed.

  14. #14
    Join Date
    Jan 2004
    Posts
    40
    sorry for being a newbie but ddl?

    and sample data? I thought that was just the field names you needed.

  15. #15
    Join Date
    Jan 2004
    Posts
    40
    Changed below as you requested:

    <% Set objConn4 = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("../Database/AmigosHC.mdb; Password=XXXXXXX")
    objConn4.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strsql4 = "SELECT SUM(ROUND_HC) AS SUM_ROUND_HC FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM tbl_HC_Data WHERE MEMBER_ID= " & Session("HC_MEM_ID") & " ORDER BY [date] DESC) AS XXX ORDER BY RoundHC) AS YYY "
    Set objRS4 = objConn4.Execute(strsql4) %>


    Got this error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

    /HCTracker/Test.asp, line 44

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •