How do I get non-tieing max records?

[notyelf]

Ok so I have this issue I can not get resolved.

There is a list of statistics on a table. They are then grouped by a
category number (1 through 10). I want to select only the max record for each
category on the table, but only if it is unique and not a tie.

For instance category 1 has 5 records, 55,56,57,58,59. The query would
select 59 (obvious and easy). However, lets say category 2 has 5 records
55,56,57,58, and 58, I want the query to return NOTHING for category 2.

If someone could please help me with this, it would be much appreciated =)

 

[Lord Kelvan]

whast your table structure because you would use something like

SELECT myTable.category, Max(myTable.record) AS MaxOfrecord
FROM myTable
GROUP BY myTable.category
HAVING (((myTable.category) Not In (SELECT myTable.category FROM
myTableGROUP BY myTable.category, myTable.record HAVING
(((Count(Table5.rec))>1)))));

what that does is the sub query returns all the records and counts up
the duplicates and returns only the duplicate categories then it
removes the duplicate categories from the master query in doing so you
should only get the requires data

my test data was
pk cat rec
1 1 1
2 1 3
3 1 3
4 2 4
5 2 5
6 2 6
7 3 7
8 3 8
9 3 9
10 4 10
11 4 11
12 4 12
13 5 13
14 5 15
15 5 15

my resaults were
cat MaxOfrec
2 6
3 9
4 12

if you give me your table structure and what columns you want in your
query i can help convert this into what you need