Solved

Select top x rows (ingoring ties)

Posted on 2006-07-20
30
434 Views
Last Modified: 2009-07-29
HI,
I have a db of league scores for a golf club. I want to return a player's top 5 scores.

When I do a SELECT TOP 5 SCORES FROM SCORES query, if the fifth row is a tie, I get more than 5 rows back. To illustrate, say the scores are as follows:

Andy -4
Andy -3
Andy -3
Andy 0
Andy 1
Andy 1
Andy 1
Andy 2

I will get 7 rows back, because of the ties.

That's no good for my purposes! I want the top 5 ONLY. Does anyone know a way I can do this? I really want to do it in the query, but if I must resort to VBA then so be it.

Thanks,

Andy
0
Comment
Question by:acotgreave
  • 13
  • 7
  • 4
  • +4
30 Comments
 
LVL 3

Expert Comment

by:cdemir
ID: 17144247
hi,

you can sort the query with ORDER BY clause, then you can fetch the top 5 record in a loop.
0
 

Author Comment

by:acotgreave
ID: 17144338
"in a loop" - you mean in a macro/VBA module?

That's what I am trying to avoid - I am developing this in Access, but want something that can be easily ported to MySQL at a later date. If it can all be wrapped in queries/sub-queries, then I will have acheived the goal.
0
 
LVL 4

Expert Comment

by:Carl2002
ID: 17144343
If you sort the table into ascending order rather than sorting in the query and then do a top 5 query the results will only be the first 5 records.

Any help?

Carl.
0
 

Author Comment

by:acotgreave
ID: 17144378
Hi Carl,
I'm not sure that would work. The table actually contains scores for all players, and I am doing a top n scores per group (where a group is an individual player). Therefore my query is a little more complex than in my original post - it selects the top ten of each group of playerids in the table (I can't post the full SQL of the query because it's not on this PC - will do that later on if still necessary)

The only way to get an individual player's scores into a separate table that could be sorted as you describe would be to do a separate query for each player. That won't work, because the number of players needs to be flexible as the leagues change.

Andy
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17144528
In order for this to work, u need a unique ID, preferably a autonumber
say u had a table called tblScores
it contains
ID (autonumber)
Name (text)
Score (number)


SELECT a.name,a.score
from tblScores a
where dcount("id","tblScores","id<" & a.id & " and name = '" & a.name & "'")  < 5
ORDER BY a.name, a.score

If u want to change your ordering, just add DESC
ORDER BY a.name, a.score DESC

remember table alias is important
0
 

Author Comment

by:acotgreave
ID: 17144590
Hi rockiroads,
This is the second qu you've answered for me in as many days.
I've never come across dcount before, and won't be able to test this until later today. Looking at it now, I can _just_ about work out what it's going to do. Looks potentially good!
Andy
0
 
LVL 6

Expert Comment

by:AHMKC1
ID: 17144636
Try this

DISTINCT SELECT TOP 5 SCORES FROM SCORES

is it serve you purpose
0
 
LVL 6

Expert Comment

by:AHMKC1
ID: 17144639
sorry sql is "SELECT DISTINCT TOP 5 SCORES FROM SCORES "
0
 

Author Comment

by:acotgreave
ID: 17144685
AHMKC1,
Distinct doesn't work because the top ten scores may not be distinct!

A player may have achieved a 3-under par (-3) nine times in his top ten scores - I need them all!

Andy
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17147700
OK this question perhaps a the classical way... (for now not worrying about ties)...
Suppose qrySCORES (as having the same field and table/query name is not ideal).
Fields: RoundID, Member & Score.

SELECT  S.Member, S.Score
FROM qrySCORES S
    WHERE  S.RoundID In
        (
         SELECT TOP 5 A.RoundID FROM qrySCORES A  WHERE  A.Member = S.Member ORDER BY Score DESC
         )
ORDER BY S.Member,  S.Score

But that *will* potentially return duplicates.
So you have to decide how to handle those.  You just want to cut off any duplicates? So in your above example you'd get back
-4, -3, -3, 0, 1
(and the rest of the 1's ignored).  Yes?

I'm not 100% convinced Rocki's method will work.  (Sorry Rocki ;-).
Though I see the idea behind it.

You also discount taking a Grouping of such a produced list - as you might then end up with fewer than 5 results.  Understood.

So, without delving into Query Joining query, amongst subquery (taking the potentially abbreviated groupings on some and joining to others) to try to get the result you want - if you're willing to accept the (without question) performance hit of a data grabbing function then you should get the results you need.

(Bear in mind that user defined functions can only be used internally to Access - external Jet queries won't pick them up).

Create the function in a standard module

Function fGetTop5(strMember As String) As String

    fGetTop5 = CurrentProject.Connection.Execute("SELECT TOP 5 RoundID From qrySCORES WHERE Member = '" & strMember & "' ORDER BY Score DESC").GetString(2, 5, , ",")
   
End Function


And use a query with

SELECT S.Member, S.Score
FROM qrySCORES AS S
WHERE "," & fGetTop5(S.Member) Like  "*," & S.RoundID & ",*"
ORDER BY S.Member, S.Score DESC;

Slower - no question.
Effective?  Should be yup.
0
 

Author Comment

by:acotgreave
ID: 17147884
Wow! Thanks for that hopefully definitive answer LP.

You were right in the assumption that I want -4, -3, -3, 0, 1 as results - just five rows, however many ties there are.

I'll investigate your solution when I get home (the golf league db is a personal project. oh to be paid to make golf summer league databases! actually, if I do it properly, maybe I could!!)

will get back to you all when your ideas are tested. if it's not tonight, it won't be until Tuesday at the earliest. (I'm off to play golf for the weekend. Oh, and it's not ball golf, it's DISC golf: http://www.bdgaopen.co.uk/press.htm)

Cheers,

Andy

0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 17147921
Include something else in the query that is unique - say the score ID or date/time:

  SELECT TOP 5
    SCORES,
    DATEGAME
  FROM
    SCORES
  ORDER BY
    SCORES DESC,
    DATEGAME DESC;

/gustav
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
ID: 17148119
Nuts forehead hard.  Thankyou!  Been racking my brains to think how I've done that far more easily in the past.

I wouldn't even bother to include it in the returned result fields (which is why I'd missed it) - and it still needs to be included as a subquery - but that's simple enough.

SELECT  S.Member, S.Score
FROM qrySCORES S
    WHERE  S.RoundID In
        (
         SELECT TOP 5 A.RoundID FROM qrySCORES A  WHERE  A.Member = S.Member ORDER BY Score DESC, RoundID
         )
ORDER BY S.Member,  S.Score

Simpler than the previous answer I gave (which would have worked but is more effort).
That will work.
Full credit to Gustav.
I'm going for a drink!

<wanders off mumbling to himself>
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 17148182
Cheers!
I could need one as well ... working in 31 degrees Celsius.

/gustav
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17149378
Hi again acotgreave. after a brief spell with IE this morning, I left the house. Spent the day looking for a new car, not good especially as Ive been ill recently so not much EE activity. Long story, someone went into back of me, car written off, too expensive to fix, afterall its only worth 1k, bought it back, but failed MOT with other failings, damn
Well Im churning thru these email notifications and posts and yes, I guess it should of clicked when u said in the question title IGNORE TIES!!! - I blame it on my illness :)

Ok, yes, I see Leigh's on the case, with help from Gustav

Right back to my original query, slight tweak. Make use of subqueries in a subquery!
Using my assumption on the table before
Here is my effort

SELECT a.name, a.score
FROM (SELECT DISTINCT Name, Score FROM tblScores) AS a
WHERE (SELECT Count(Score) FROM (SELECT DISTINCT Name, Score FROM tblScores) AS b WHERE b.Name = ORDER BY a.name, a.score;


I would use DCOUNT as before, but then I would of had to create another query and base DCOUNT on that

here is my data in tblScores

Name      Score
Andy      -4
Andy      -3
Andy      -2
Andy      -1
Andy      0
Andy      0
Andy      2
Andy      3
Andy      -4
George      6
George      6
George      1
George      0
George      2
George      2
George      3
George      3
George      4
George      4
George      5
George      5


and the query produced this

name      score
Andy      -4
Andy      -3
Andy      -2
Andy      -1
Andy      0
George      0
George      1
George      2
George      3
George      4




By the way, Leigh, no offence taken, just too damn tired too think anyway

Oh and one more thing, anyone know where I can pick up a decent Mondeo, new shape 01 onwards would be okay. Though Ive only got about 3k to spend. I need to car by Monday so perhaps I can get to work!
Thats my job tomorrow, looking for a new car
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17149402
Urm, cut/paste error in my SQL, ooops
I did wonder why it looked wrong. Im definitely more ill than I first thought!


SELECT a.name, a.score
FROM (SELECT DISTINCT Name, Score FROM tblScores) AS a
WHERE (SELECT Count(Score) FROM (SELECT DISTINCT Name, Score FROM tblScores) AS b WHERE b.Name = a.Name and b.Score < a.Score) < 5
ORDER BY a.name, a.score



0
 

Author Comment

by:acotgreave
ID: 17165918
Hi all,
Well, it's sorted, and amazingly, the ultimate solution was pretty simple. Thanks to Gustav for coming up with the answer to select an extra unique column. Amazing how the answer stares you in the face, but you don't see it.

Thanks as well to LP for then putting it into a grouping query.

And sorry to Rocki - I appreciate the effort you put in, but I guess the points have to go to the answer that worked for me!

Cheers all,

Andy
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17165940
No probs, just curious whether that sql I posted worked or not
0
 

Author Comment

by:acotgreave
ID: 17166006
Hi Rocki,
I never tested your example, because once I saw Gustav's solution, it all became clear. I'll be tweaking things tonight, so I'll give it a go then and let you know.
Andy
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17166016
Alright, thanks
0
 

Author Comment

by:acotgreave
ID: 17173858
Hi Rocki,
I tried your solutions this morning. The DCOUNT one wouldn't run due to a syntax error - i tried to work it out, but it's early and I have to go to work so have limited time, I'm afraid!

Your second solution:
SELECT a.name, a.score
FROM (SELECT DISTINCT Name, Score FROM tblScores) AS a
WHERE (SELECT Count(Score) FROM (SELECT DISTINCT Name, Score FROM tblScores) AS b WHERE b.Name = a.Name and b.Score < a.Score) < 5
ORDER BY a.name, a.score

I'm afraid this one didn't work either- it bought back distinct scores, which isn't what i'm after, unfortunately. it's a shame, because although the other solutions work, I would like to be able to change the number of scores I want to return to a user-determined number, and I know that it's difficult to use the TOP predicate with a variable number.

Andy
0
 

Author Comment

by:acotgreave
ID: 17173893
PS - I just explored your solution a bit more. If you change your tblScores table to include the following data for Andy, you'll see the problem for yourself:

ID      Name      Score
11      Andy      -4
1      Andy      -4
13      Andy      -3
12      Andy      -3
10      Andy      -3
2      Andy      -3
3      Andy      -2
4      Andy      -1
6      Andy      0
5      Andy      0
7      Andy      2
8      Andy      3

With the above data, the result set I want is:
ID      Name      Score
11      Andy      -4
1      Andy      -4
13      Andy      -3
12      Andy      -3
10      Andy      -3

The solution you tried brings back the unique records only:
name      score
Andy      -4
Andy      -3
Andy      -2
Andy      -1
Andy      0
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17180701
It isn't difficult to use TOP N if you build the SQL in code and set a form's recordsource as it - as opposed to using a saved query for direct display (never a good idea IMHO).
0
 

Author Comment

by:acotgreave
ID: 17182155

"It isn't difficult to use TOP N if you build the SQL in code and set a form's recordsource as it - as opposed to using a saved query for direct display (never a good idea IMHO)."

That's interesting - I thought it was always better to run from saved queries because they get optimized. I've never come across the idea that a query to be used for display is better run in code - would you mind elaborating?

Andy

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17200008
I just meant that using a query itself as a displaying mechanism isn't a good UI choice.
Query's should be used to fuel results into a Form or Report.  In in doing so - you can use on the fly SQL, rather than a saved query with it's saved SQL statement.

Yes - you can change the SQL statement in code of a saved query - but you'd likely lose its execution plan anyway.
(Otherwise re-used saved queries do have that slight advantage over ad hoc SQL).
0
 

Author Comment

by:acotgreave
ID: 17200781
Ah, i see what you mean now - I had completely misinterpreted what you meant!
Thanks for clarifying
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17200983
Howdo acotgreave

The query/data I did was based on name and scores only. No ID fields. This was based on the data u posted in your original question plus I thought you said ignore ties so I thought that meant unique scores.

If u wanted the duplicate scores, all u need to do is remove the word DISTINCT from my query
i.e.

SELECT a.name, a.score
FROM [SELECT Name, Score FROM tblScores]. AS a
WHERE (SELECT Count(Score) FROM (SELECT Name, Score FROM tblScores) AS b WHERE b.Name = a.Name and b.Score < a.Score) < 5
ORDER BY a.name, a.score;


so running that produces

name      score
Andy      -4
Andy      -4
Andy      -3
Andy      -3
Andy      -3
George      0
George      1
George      2
George      2
George      3
George      3
0
 

Author Comment

by:acotgreave
ID: 17201422
Hi Rocki,
I'm going to try that later. I just reread my original post, which I thought was clear that I wanted to ignore ties for the FIFTH place, ie always return 5 rows.

However, it's clear now how easy it was to misinterpret in the way you did. Hmm, I feel a little guilty that at least some of the points didn't go to you. I am sure the karma god will give you more points in another question soon!

I am trying to get my head around how your solution works, but there's just too much subquerying going on for my brain to get round!
Andy
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17201816
Andy,dont worry about the pts. Question was a good challenge of my Access sql skills

0
 

Author Comment

by:acotgreave
ID: 17206948
Hi Rocki,
I tried your solution, but I'm still not convinced it's working, I'm afraid!

Here's the contents of tblScores:
Name      Score
Andy      -3
Andy      -3
Andy      -2
Andy      -2
Andy      -2
Andy      -2
G      1
G      1
G      2
G      3
G      4
G      4
N      1
N      1
N      1
N      1
N      1
N      1

And this is the query I ran (copied from your post above):

SELECT a.name, a.score
FROM [SELECT Name, Score FROM tblScores]. AS a
WHERE (SELECT Count(Score) FROM (SELECT Name, Score FROM tblScores) AS b WHERE b.Name = a.Name and b.Score < a.Score) < 5
ORDER BY a.name, a.score;

And the result set:
name      score
Andy      -3
Andy      -3
Andy      -2
Andy      -2
Andy      -2
Andy      -2
G      1
G      1
G      2
G      3
G      4
G      4
N      1
N      1
N      1
N      1
N      1
N      1

As you'll see, I am getting six rows back for each. I want to limit the results to five best scores, irrelevant of the date of those scores. The league is based on players' top scores.

Don't worry anymore about trying to get it fixed for my benefit- i have  a working solution. Of course, I do know how these things can niggle away until they are fixed, and if this happens, I'm still keen to know if you ever work it out!

Andy
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now