469,290 Members | 1,882 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

SELECT TOP 3 Within a GROUP BY

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge
  2. FROM Data AS Dupe
  3. 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)))
  4. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  5.  
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.
Aug 16 '11 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Class]
  2.      ,   [DSQ]
  3.      ,   [Score]
  4.      ,   Dupe.*
  5. FROM     [Data] AS [Dupe]
  6. WHERE    (NOT [DSQ])
  7.   AND    ([BestOf]=0)
  8.   AND    ([Score] In (SELECT   TOP 3 [Score]
  9.                       FROM     [Data]
  10.                       WHERE    ([Class]=Dupe.Class)
  11.                         AND    ([Score]>0)
  12.                         AND    (NOT [DSQ])
  13.                         AND    ([BestOf]=0)
  14.                       ORDER BY [Score] DESC)
  15. ORDER BY [Class]
  16.        , [Score] DESC

27 5464
patjones
931 Expert 512MB
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 disqualifications and best of records.

So with that said, maybe something like this?

Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, 
  2.        Dupe.Score, 
  3.        Dupe.ID, 
  4.        Dupe.FName, 
  5.        Dupe.LName, 
  6.        Dupe.Year, 
  7.        Dupe.Make, 
  8.        Dupe.Model, 
  9.        Dupe.Judge
  10. FROM Data AS Dupe
  11. WHERE Dupe.Score <> 0 AND Dupe.Score IN (SELECT TOP 3 Data.Score 
  12.                                          FROM Data 
  13.                                          WHERE Data.DSQ <> TRUE AND Data.BestOf = 0
  14.                                          ORDER BY Data.Score DESC)
  15. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  16.  

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.
Aug 16 '11 #2
Rabbit
12,516 Expert Mod 8TB
Actually, they will need the alias because the subquery is filtering on class from the parent query.
Aug 16 '11 #3
patjones
931 Expert 512MB
Ah, right...it's a correlated sub-query! So this may change my advice?
Aug 16 '11 #4
Rabbit
12,516 Expert Mod 8TB
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.
Aug 16 '11 #5
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Score)<>0 And (Dupe.Score)
  4.   In (SELECT TOP 3 Data.Score
  5.       FROM Data
  6.       WHERE (((Data.Class)=Dupe.[Class]))
  7.       ORDER BY Data.Score DESC)))
  8. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  9.  
And this is what I think you want it to be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge, Dupe.DSQ, Dupe.BestOf
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Score)<>0 And (Dupe.Score)
  4.   In (SELECT TOP 3 Data.Score
  5.       FROM Data
  6.       WHERE (((Data.Class)=Dupe.[Class] AND (Data.DSQ)<>True AND (data.BestOf)=0))
  7.       ORDER BY Data.Score DESC)))
  8. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  9.  
Aug 17 '11 #6
I should also say that Data.DSQ is a Yes/No Check Box in the form and in the table.
Aug 17 '11 #7
patjones
931 Expert 512MB
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
Aug 17 '11 #8
NeoPa
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1.       WHERE (Data.Class=Dupe.Class)
  2.         AND (NOT Data.DSQ)
  3.         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.
Aug 17 '11 #9
NeoPa
32,173 Expert Mod 16PB
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.
Aug 17 '11 #10
patjones
931 Expert 512MB
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.
Aug 17 '11 #11
NeoPa
32,173 Expert Mod 16PB
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 :-)
Aug 17 '11 #12
patjones
931 Expert 512MB
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.
Aug 17 '11 #13
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...
Attached Files
File Type: zip HeatWaveInc.zip (1.15 MB, 86 views)
Aug 17 '11 #14
patjones
931 Expert 512MB
I'm having trouble linking to or even opening the table 'Data'...maybe NeoPa will be able to do it.
Aug 17 '11 #15
NeoPa
32,173 Expert Mod 16PB
@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.
Aug 17 '11 #16
well, I have discovered that this returns the disqualified record only if it is a tie score with another in the top 3.
Aug 18 '11 #17
It now works like this...

Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Class)<>"**** - U n k n o w n - NEEDS CLASSIFICATION")
  4.    AND ((Dupe.DSQ)=False)
  5.    AND ((Dupe.Score)<>0
  6.    AND  (Dupe.Score) In (SELECT TOP 3 Data.Score
  7.                          FROM Data
  8.                          WHERE (((Data.Class)=(Dupe.Class)
  9.                             AND ((Data.DSQ)=False)
  10.                             AND  (Data.BestOf)=0))
  11.                          ORDER BY Data.Score DESC)))
  12. ORDER BY Dupe.Class, Dupe.Score DESC;
  13.  
I had to add a second ((Dupe.DSQ)=False) to filter disqualified from the results. I just don't understand why they were returned at all.
Aug 18 '11 #18
BTW, Access changes 0/1 to False/True when you save the SQL...
Aug 18 '11 #19
NeoPa
32,173 Expert Mod 16PB
@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.
Aug 18 '11 #20
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:
Expand|Select|Wrap|Line Numbers
  1. ID                    Class                        Score   DSQ   BestOf
  2. 20    **** - U n k n o w n - NEEDS CLASSIFICATION   16    False    0
  3. 10    **** - U n k n o w n - NEEDS CLASSIFICATION    0    False    0
  4. 11    ACCF - American Compact Car Full              81    False    0
  5.  2    ACCF - American Compact Car Full              62     True    0
  6.  6    ACCF - American Compact Car Full              62    False    0
  7. 12    ACCF - American Compact Car Full              62    False    0
  8. 16    ACCF - American Compact Car Full              62    False    0
  9.  3    ACCF - American Compact Car Full              40    False    0
  10. 13    ACCF - American Compact Car Full              40    False    0
  11. 14    ACCF - American Compact Car Full              24    False    0
  12.  4    ACCF - American Compact Car Full              21    False    0
  13.  1    ACCF - American Compact Car Full               0    False    1
  14. 19    AM - Antique Modified (1949 and earlier)     101    False    1
  15.  9    AM - Antique Modified (1949 and earlier)     100    False    2
  16.  5    AM - Antique Modified (1949 and earlier)      81     True    0
  17. 15    AM - Antique Modified (1949 and earlier)      80    False    0
  18.  7    AM - Antique Modified (1949 and earlier)      40    False    0
  19. 17    AM - Antique Modified (1949 and earlier)      40    False    0
  20.  8    AM - Antique Modified (1949 and earlier)      23    False    0
  21. 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):
Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Class)<>"**** - U n k n o w n - NEEDS CLASSIFICATION")
  4.    AND ((Dupe.DSQ)=False)
  5.    AND ((Dupe.Score)<>0
  6.    AND  (Dupe.Score) In (SELECT TOP 3 Data.Score
  7.                          FROM Data
  8.                          WHERE (((Data.Class)=(Dupe.Class)
  9.                             AND ((Data.DSQ)=False)
  10.                             AND  (Data.BestOf)=0))
  11.                          ORDER BY Data.Score DESC)))
  12. ORDER BY Dupe.Class, Dupe.Score DESC;
The results are here:

Expand|Select|Wrap|Line Numbers
  1. ID                    Class                        Score   DSQ   BestOf
  2. 11    ACCF - American Compact Car Full              81    False    0
  3.  6    ACCF - American Compact Car Full              62    False    0
  4. 12    ACCF - American Compact Car Full              62    False    0
  5. 16    ACCF - American Compact Car Full              62    False    0
  6.  
  7. 15    AM - Antique Modified (1949 and earlier)      80    False    0
  8.  7    AM - Antique Modified (1949 and earlier)      40    False    0
  9. 17    AM - Antique Modified (1949 and earlier)      40    False    0
Without ((Dupe.DSQ)=False) 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...
Aug 18 '11 #21
Rabbit
12,516 Expert Mod 8TB
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.
Aug 18 '11 #22
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.
Aug 18 '11 #23
Rabbit
12,516 Expert Mod 8TB
Data
Expand|Select|Wrap|Line Numbers
  1. DSQ Score
  2. Y   90
  3. N   90
SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT Score
  2. FROM tableName
  3. WHERE DSQ = 'N'
Results
Expand|Select|Wrap|Line Numbers
  1. Score
  2. 90
SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT DSQ, Score
  2. FROM tableName
  3. WHERE Score IN (previous query)
  4.  
  5. is the same as
  6.  
  7. SELECT DSQ, Score
  8. FROM tableName
  9. WHERE Score IN (90)
Results
Expand|Select|Wrap|Line Numbers
  1. DSQ Score
  2. Y   90
  3. N   90
Aug 18 '11 #24
NeoPa
32,173 Expert Mod 16PB
@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).
Aug 18 '11 #25
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).
Aug 18 '11 #26
NeoPa
32,173 Expert Mod 16PB
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 :
    Expand|Select|Wrap|Line Numbers
    1. 81
    2. 62
    3. 62
    4. 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).
Aug 18 '11 #27
NeoPa
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Class]
  2.      ,   [DSQ]
  3.      ,   [Score]
  4.      ,   Dupe.*
  5. FROM     [Data] AS [Dupe]
  6. WHERE    (NOT [DSQ])
  7.   AND    ([BestOf]=0)
  8.   AND    ([Score] In (SELECT   TOP 3 [Score]
  9.                       FROM     [Data]
  10.                       WHERE    ([Class]=Dupe.Class)
  11.                         AND    ([Score]>0)
  12.                         AND    (NOT [DSQ])
  13.                         AND    ([BestOf]=0)
  14.                       ORDER BY [Score] DESC)
  15. ORDER BY [Class]
  16.        , [Score] DESC
Aug 18 '11 #28

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Dominic Tocci | last post: by
3 posts views Thread by Leslie A Rhorer | last post: by
2 posts views Thread by news.symantec.com | last post: by
omerbutt
3 posts views Thread by omerbutt | last post: by
2 posts views Thread by swethak | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.