select to 3 within a group

[ping99]

Hi all,

I have a mytable with 2 fields:

team score
AAAA 10
AAAA 12
AAAA 14
AAAA 16
AAAA 18
AAAA 20
AAAA 22

BBBB 11
BBBB 22
BBBB 33
BBBB 44
BBBB 55
BBBB 66

CCCC 22
CCCC 77
CCCC 88
CCCC 99

DDDD 10
DDDD 12

to have in mynewtable AT LEAST the top 3 in each team if that team has more
than 3 records.

team score
AAAA 22
AAAA 20
AAAA 18


BBBB 66
BBBB 55
BBBB 44

CCCC 99
CCCC 88
CCCC 77

DDDD 12
DDDD 10

I try this SQL statement but not work.


SELECT TOP 3 TEAM,SCORE FROM MYTABLE WHERE TEAM IN ( SELECT TEAM,SCORE FROM MYTABLE ORDER BY TEAM, SCORE DESC;
Please assist to correct my sql.

TIA

 

[TheAceMan1]

How are ya ping99 . . .

Have you tried a [blue]Union[/blue] query?

See Ya! . . . . . .

 

[ping99]

Hi TheAceman1,

I have not tried a Union query ?

Would you please guide me how to start if you can in SQL

because this is only the selection of top 3 within a group

of many groups.

Thanks for your prompt reply.

 

[TheAceMan1]

ping99 . . .

Apparently you have too many groups to make a union query feasible. There is a method where this can be done using a sum query for reports, however the method is dependent on the Primary Key returned by the report. Add to this you want to clear and update [blue]newTable![/blue]

If I don't find anything else in my resources it can be done in code using SQL. In the meantime:
[ol][li]If the tables don't have a PK, make it so (since this is an apparent independent DB use [blue]autonumber[/blue]).[/li]
[li]Whats the actual names of the tables & fields?[/li][/ol]
You'll need to add a [blue]Command Button[/blue] somewhere to run the code . . .

See Ya! . . . . . .

 

[TheAceMan1]

ping99 . . .

Search is not coming up with anything so I'll just go with code.

Copy/paste the following to the [blue]On Click[/blue] event of the [blue]command button[/blue] (you substitute proper table/field names):

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim sqlTeams As String, sqlTop3 As String
   
   Set db = CurrentDb
   sqlTeams = "SELECT Team " & _
              "FROM myTable " & _
              "GROUP BY Team " & _
              "ORDER BY Team;"
   Set rst = db.OpenRecordset(sqlTeams, dbOpenDynaset)
   
   Do Until rst.EOF
      sqlTop3 = "INSERT INTO newTable (Team, Score) " & _
                "SELECT TOP 3 myTable.Team, myTable.Score " & _
                "FROM myTable " & _
                "WHERE (myTable.Team = '" & rst!Team & "') " & _
                "ORDER BY myTable.Score DESC;"
      DoCmd.RunSQL sqlTop3
      rst.MoveNext
   Loop
   
   Set rst = Nothing
   Set db = Nothing[/blue]

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

See Ya! . . . . . .

 

[ping99]

Thanks TheIceMan1,

Your code works great, I really appreciated your help.

Ping

 

[TheAceMan1]

ping99 . . .

Just a quickie. Add the line in [purple]purple[/purple] where you see it. This deletes the records in newTable in preparation for the new values.

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim sqlTeams As String, sqlTop3 As String
   
   Set db = CurrentDb
   [purple][b]DoCmd.RunSQL "DELETE newTable.* FROM newTable;"[/b][/purple]

   sqlTeams = "SELECT Team " & _
              "FROM myTable " & _
              "GROUP BY Team " & _
              "ORDER BY Team;"
   Set rst = db.OpenRecordset(sqlTeams, dbOpenDynaset)
   
   Do Until rst.EOF
      sqlTop3 = "INSERT INTO newTable (Team, Score) " & _
                "SELECT TOP 3 myTable.Team, myTable.Score " & _
                "FROM myTable " & _
                "WHERE (myTable.Team = '" & rst!Team & "') " & _
                "ORDER BY myTable.Score DESC;"
      DoCmd.RunSQL sqlTop3
      rst.MoveNext
   Loop
   
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .