SELECT TOP 3 Within a GROUP BY

[NeoPa]

@Pat
I'll check it out. If I run out of time now I'll try it later. If I can, I will.

@Daniel
You should be posting the information in the thread. Attachments make a thread almost useless for understanding what the problem is as well as any appropriate solutions. The information you describe can easily be displayed in the post with a modicum of effort. You should only consider posting your project as an attachment when requested to do so by an expert or other member.

Nevertheless, I'll do what I can to post this for you soon if you remember these points for next time ;-)

PS. No joy. It's in 2007 or 2010 format. See Attach Database (or other work) for when you need to post a project.

In the mean time, please post the example data with an explanation of which record(s) behaved other than as expected.

[Daniel Yantis]

well, I have discovered that this returns the disqualified record only if it is a tie score with another in the top 3.

[Daniel Yantis]

It now works like this...

SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
FROM Data AS Dupe
WHERE (((Dupe.Class)<>"**** - U n k n o w n - NEEDS CLASSIFICATION")
   AND ((Dupe.DSQ)=False)
   AND ((Dupe.Score)<>0
   AND  (Dupe.Score) In (SELECT TOP 3 Data.Score
                         FROM Data
                         WHERE (((Data.Class)=(Dupe.Class)
                            AND ((Data.DSQ)=False)
                            AND  (Data.BestOf)=0))
                         ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC;

I had to add a second ((Dupe.DSQ)=Fal se) to filter disqualified from the results. I just don't understand why they were returned at all.

[Daniel Yantis]

BTW, Access changes 0/1 to False/True when you save the SQL...

[NeoPa]

@Daniel
I guess that you're not going to post the data as requested now. Having been asked twice and you haven't yet, I see the chances fading into nothingness.

That's a shame, as your SQL is clearly not the same as was suggested, so I wouldn't expect it to work necessarily. Personally, I see no good reason to put further effort into explaining the score for someone who isn't co-operating even in their own thread.

[Daniel Yantis]

hmmm...

I see you edited your post and said it was in Access 2010. I first read you would look at my attachment. I see the "In the meantime" now. But I don't see where I was asked twice?

Sorry, but the sarcasm most certainly will not help. It is clear that I am a noob and your assistance is greatly appreciated. Please work on being patient with people.

Anyway, how to I post the example data? Copy the table data and the records in it and then copy the report view? This is why I sent the file up; I felt like the data was too much to try and display.

So here is the data:

[B][U]ID                    Class                        Score   DSQ   BestOf[/U][/B]
20    **** - U n k n o w n - NEEDS CLASSIFICATION   16    False    0
10    **** - U n k n o w n - NEEDS CLASSIFICATION    0    False    0
11    ACCF - American Compact Car Full              81    False    0
 2    ACCF - American Compact Car Full              62     True    0
 6    ACCF - American Compact Car Full              62    False    0
12    ACCF - American Compact Car Full              62    False    0
16    ACCF - American Compact Car Full              62    False    0
 3    ACCF - American Compact Car Full              40    False    0
13    ACCF - American Compact Car Full              40    False    0
14    ACCF - American Compact Car Full              24    False    0
 4    ACCF - American Compact Car Full              21    False    0
 1    ACCF - American Compact Car Full               0    False    1
19    AM - Antique Modified (1949 and earlier)     101    False    1
 9    AM - Antique Modified (1949 and earlier)     100    False    2
 5    AM - Antique Modified (1949 and earlier)      81     True    0
15    AM - Antique Modified (1949 and earlier)      80    False    0
 7    AM - Antique Modified (1949 and earlier)      40    False    0
17    AM - Antique Modified (1949 and earlier)      40    False    0
 8    AM - Antique Modified (1949 and earlier)      23    False    0
18    AM - Antique Modified (1949 and earlier)      20    False    0

Here is the current SQL to display a winner's report (top 3 not disqualified not in 'best of' must be scored and must be classified):

SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
FROM Data AS Dupe
WHERE (((Dupe.Class)<>"**** - U n k n o w n - NEEDS CLASSIFICATION")
   AND ((Dupe.DSQ)=False)
   AND ((Dupe.Score)<>0
   AND  (Dupe.Score) In (SELECT TOP 3 Data.Score
                         FROM Data
                         WHERE (((Data.Class)=(Dupe.Class)
                            AND ((Data.DSQ)=False)
                            AND  (Data.BestOf)=0))
                         ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC;

The results are here:

[B][U]ID                    Class                        Score   DSQ   BestOf[/U][/B]
11    ACCF - American Compact Car Full              81    False    0
 6    ACCF - American Compact Car Full              62    False    0
12    ACCF - American Compact Car Full              62    False    0
16    ACCF - American Compact Car Full              62    False    0

15    AM - Antique Modified (1949 and earlier)      80    False    0
 7    AM - Antique Modified (1949 and earlier)      40    False    0
17    AM - Antique Modified (1949 and earlier)      40    False    0

Without ((Dupe.DSQ)=Fal se) before the Select top 3 it still returns record 2 which I don't see why would be returned as a record at all since it should be filtered inside the top 3...

[Rabbit]

It's filtered out in the subquery but the subquery is still returning the score 62. And that's what the parent query is using to filter the records by.

[Daniel Yantis]

If it is filtered out in the subquery, why is it returning the score at all?
Please remember I am a noob...

The way I think I understand it is this:
The subquery returns records to the main query based on the subquery criteria. Therefore, the main query should only have available to it the records that are not disqualified.

Where am I messed up here? I need to understand A LOT more.

[Rabbit]

Data

DSQ Score
Y   90
N   90

SQL

SELECT Score
FROM tableName
WHERE DSQ = 'N'

Results

Score
90

SQL

SELECT DSQ, Score
FROM tableName
WHERE Score IN (previous query)

is the same as

SELECT DSQ, Score
FROM tableName
WHERE Score IN (90)

Results

DSQ Score
Y   90
N   90

[NeoPa]

@Daniel
The original request was in post #9, but I can see that I may have been at fault for misreading the situation after I had updated an earlier post of mine. That was an oversight which I regret (as much as for how it makes me look stupid as for how I was being less than reasonable to you).

Daniel Yantis:
Sorry, but the sarcasm most certainly will not help. It is clear that I am a noob and your assistance is greatly appreciated. Please work on being patient with people.

Not sarcasm. An explanation of why I'd decided to discontinue my involvement under those circumstances.

As I see I was mistaken in my reading of the situation, I am happy to continue that involvement. Missing one request for info is unfortunate, but common enough for me not to lose sleep over. Frankly, if you had ignored both requests I would feel quite content to ignore the thread going forward. There is only so much I'm prepared to tolerate from people who I'm trying to help.

I see you already have Rabbit on the thread helping now. That is generally good news for you. Few have as good an understanding of SQL as he. I will now reformat your post so that it's a little easier to read (No criticism implied here. I'm good at that and most people struggle - but I find it much easier to work with data I can see clearly). If there is anything further to post after I've had a chance to look through it all, then I will do so then, and I promise not to add it as a PS to a previous post this time (In my defense I was trying to avoid lots of silly posts that make a thread hard to read, but caused more problems than I avoided obviously).

[Daniel Yantis]

FOR REAL: your help is truly appreciated!
I think I got this question answered now.
About a year ago I worked on this same project but lost the whole thing. So I am having to relearn some and remember what I did.

Thank you for being patient to a noob!

I have the next question using this SQL in a new topic (Mod Edit - See Identify Ties).

[NeoPa]

It looks like you have this sorted now Daniel. Rabbit has explained quite graphically why you saw what you did. I must admit my earlier suggestions didn't allow for matching invalid records based on multiple records sharing the same score. Your solution was a logical one.

There are basically two issues to consider here :

1. The TOP predicate does not guarantee the return of only X records. In your case the result set would actually have been :
   

   81
   62
   62
   62

   Four values not three. This is largely irrelevant though as the IN() construct simply verifies if the value in the current record being processed matches any one of those in the list, or not.
2. This check though, only ensures that records that don't match any of these scores are discarded. In no way does it ensure that only the records which went to make up the list are the only ones not discarded. Record (whose [ID] =) 2 has a score which matches one of the score values returned. Unless this is separately discarded due to [DSQ] being TRUE, it will (erroneously) be included in the result set (This point was overlooked in my earlier suggestion of course).

[NeoPa]

While working on your other question I realised you also need a check for [BestOf] = 0 in your outer query. You fell over the [DSQ] problem, but the issue applies to both :

SELECT   [Class]
     ,   [DSQ]
     ,   [Score]
     ,   Dupe.*
FROM     [Data] AS [Dupe]
WHERE    (NOT [DSQ])
  AND    ([BestOf]=0)
  AND    ([Score] In (SELECT   TOP 3 [Score]
                      FROM     [Data]
                      WHERE    ([Class]=Dupe.Class)
                        AND    ([Score]>0)
                        AND    (NOT [DSQ])
                        AND    ([BestOf]=0)
                      ORDER BY [Score] DESC)
ORDER BY [Class]
       , [Score] DESC