SELECT TOP 3 Within a GROUP BY

[Daniel Yantis]

Here is what I have for a report that selects the top 3 winners:

SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge
FROM Data AS Dupe
WHERE (((Dupe.Score)<>0 And (Dupe.Score) In (SELECT TOP 3 Data.Score FROM Data WHERE (((Data.Class)=Dupe.[Class])) ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;

I need to add a filter in the select top 3 where if the record is disqualified it will not show up and select the next top 3.

Also a filter that if the record is marked as 'best of' it will not show up in the top 3 and select the next top 3.

Data.DSQ is True / False
Data.BestOf is numeric value.

So I want to filter out Data.DSQ = True and Data.BestOf > 0 from the Select Top 3 records.

I have tried this many ways and I can't figure it out. The problem I have is that I can filter these out, but it will not just skip them: it does not continue to select the next records to total a Top 3.

ie: if 3 records and two are disqualified, I only return 1 record and not the next in the top 3 list.

[patjones]

I think the problem lies in the WHERE clause in the sub-query. I don't think the alias 'Dupe' should appear in there, and rather that's the spot to filter out your disqualificatio ns and best of records.

So with that said, maybe something like this?

SELECT Dupe.Class, 
       Dupe.Score, 
       Dupe.ID, 
       Dupe.FName, 
       Dupe.LName, 
       Dupe.Year, 
       Dupe.Make, 
       Dupe.Model, 
       Dupe.Judge
FROM Data AS Dupe
WHERE Dupe.Score <> 0 AND Dupe.Score IN (SELECT TOP 3 Data.Score 
                                         FROM Data 
                                         WHERE Data.DSQ <> TRUE AND Data.BestOf = 0
                                         ORDER BY Data.Score DESC)
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;

I'd also point out that it's not really necessary to alias the table in this situation, unless you have some plans for it outside the context of your question that would require aliasing. So I left it as is.

[Rabbit]

Actually, they will need the alias because the subquery is filtering on class from the parent query.

[patjones]

Ah, right...it's a correlated sub-query! So this may change my advice?

[Rabbit]

No, the SQL is fine, they'll just need to add back in the link.

Edit: In the SQL, it should be >0 and not =0.

[Daniel Yantis]

Well.... I'm confused.
Your SQL does not filter out disquaified even after I make Rabbit's edit.

This is what I have as a fresh working statement:

SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge
FROM Data AS Dupe
WHERE (((Dupe.Score)<>0 And (Dupe.Score)
  In (SELECT TOP 3 Data.Score
      FROM Data
      WHERE (((Data.Class)=Dupe.[Class]))
      ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;

And this is what I think you want it to be:

SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge, Dupe.DSQ, Dupe.BestOf
FROM Data AS Dupe
WHERE (((Dupe.Score)<>0 And (Dupe.Score)
  In (SELECT TOP 3 Data.Score
      FROM Data
      WHERE (((Data.Class)=Dupe.[Class] AND (Data.DSQ)<>True AND (data.BestOf)=0))
      ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;

[Daniel Yantis]

I should also say that Data.DSQ is a Yes/No Check Box in the form and in the table.

[patjones]

OK, that's an important piece of information. Instead of putting Data.DSQ <> TRUE, put Data.DSQ = 0 and see if that changes things.

Pat

[NeoPa]

I could redo the WHERE clause on line #6 of the suggested SQL, but I don't see where what is posted would be expected to fail :

      WHERE (Data.Class=Dupe.Class)
        AND (NOT Data.DSQ)
        AND (Data.BestOf=0)

Perhaps you could post some example data of where this (the latter set of SQL you posted - the one you think we want it to be) is not working as expected/requested. I must admit I can't imagine what's going wrong as the SQL looks about right to me.

[NeoPa]

ZeppHead80:
Instead of putting Data.DSQ <> TRUE, put ...

I absolutely agree with Pat's point. You should never, ever compare a boolean value or variable to TRUE if you want to determine whether it is TRUE or FALSE. It's stored as an integer and all non-zero integers are treated as TRUE for boolean purposes, yet only -1 is actually equal to the value TRUE. Hence comparison with TRUE is a misleading comparison and (obviously) can give misleading results.

[patjones]

I've found that in some circumstances using TRUE or FALSE in check box comparisons doesn't work; particularly in SQL WHERE clauses. This is why I am suggesting that we try Data.DSQ = 0.

[NeoPa]

I'm confused as to what that is a response to Pat (probably due to the quick-fire posts at that time) but I'll try to explain clearly :

As I said in post #10, booleans should never be compared to TRUE or FALSE. Apart from the unreliability of the logic, it is also never required. What's the point, after all, of checking a boolean value, simply to produce the boolean value required. It's already boolean. What's to check. The fact that this can, in some circumstances, produce a non-intuitive result, is further cause for handling with caution.

In post #9, the boolean value is used as a boolean, but it's also NOTted (not knotted which is something else entirely). This is the way I would always suggest they be used.

Checking a boolean value against 0 (or FALSE) is perfectly reliable. It will always return a value that matches its boolean value (IE. TRUE if <> 0 and FALSE if = 0). Due to VBA's handling of booleans, arithmetic with booleans is not as straightforward as Boolean Arithmetic. Using it logically can ensure that mistakes are avoided. The most common problems are caused by comparing anything to TRUE (Technically True in VBA but I've used TRUE for emphasis throughout) or even -1 as seen in some cases.

I hope you appreciate that I'm posting this for clarity and not as an attempt to be critical. Hopefully you see this as support for the point you were making - even if we seem to have approached this same point from slightly different angles :-)

[patjones]

Your post appeared immediately after I hit 'Post Reply'. We essentially posted at the same time. But yes, your point is well taken. The 'shorthand' method as opposed to an explicit comparison with 0 makes a lot of sense.

[Daniel Yantis]

Thank you all for the comments.
The SQL does seems to work now.

However, I am having a filter issue with Data.DSQ that I cannot figure out.

I am attaching the program to help explain...
Car #5 should not be showing up in the Winner's Report as it is Disqualified. The other 2 cars that are disqualified are filtered correctly. What's up with #5?

p.s. you will need to link the Data file using linked table manager. I also realize this is a messy program; I'm not very good at this. Furthermore, the Ties report will be my next issue...

[patjones]

I'm having trouble linking to or even opening the table 'Data'...maybe NeoPa will be able to do it.