Having and Group By Issues

[Daniel Yantis]

I need help adding a HAVING statement to my current working SQL.

Here is my data:

ID    Class  Score   DSQ   BestOf
11    ACCF     81   False    0
 2    ACCF     62    True    0
 6    ACCF     62   False    0
12    ACCF     62   False    0
16    ACCF     62   False    0
 3    ACCF     40    True    0
13    ACCF     40   False    0
14    ACCF     24   False    0
 4    ACCF     21   False    0
 1    ACCF      0   False    0
10    AM      101   False    1
 9    AM      100   False    2
 5    AM       81    True    0
15    AM       80   False    0
 7    AM       40   False    0
17    AM       40   False    0
 8    AM       23   False    0
18    AM       20   False    0

Here is my working SQL:

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.BestOf)=0)
  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
                         AND Data.Score>0))
                       ORDER BY Data.Score DESC)))
GROUP BY Dupe.Class, Dupe.Score
HAVING (Count(*)>1)
ORDER BY Dupe.Class, Dupe.Score DESC;

Here is the result of the above SQL on the data:

ID    Class  Score   DSQ   BestOf
11    ACCF     81   False    0
 6    ACCF     62   False    0
12    ACCF     62   False    0
16    ACCF     62   False    0
15    AM       80   False    0
 7    AM       40   False    0
17    AM       40   False    0

This is what I want:

ID    Class  Score   DSQ   BestOf
 6    ACCF     62   False    0
12    ACCF     62   False    0
16    ACCF     62   False    0
 7    AM       40   False    0
17    AM       40   False    0

Basically I need to see a list of tie scores grouped by class.

 

[jdraw]

Try the following

SELECT Dupe.ID
, Dupe.Class
, Dupe.Score
, Dupe.DSQ
, Dupe.BestOf
FROM data AS Dupe
WHERE (((Dupe.DSQ)="False") AND
((Dupe.BestOf)=0) AND
((Dupe.Score)>0 And
(Dupe.Score) In (
SELECT TOP 3 Score
FROM data
WHERE
dsq ="false" and
Bestof =0 and
data.class = dupe.class
Group by class,score
Having count(*) >1
)))
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;

 

[Daniel Yantis]

data type mismatch in criteria expression...
fixed by taking quotes off "false"

and that does appear to work...

 

[Daniel Yantis]

No, not working completely.
It's not select top 3 ties.
It is selecting all ties.