Query for most popular result of query 1

[xhonzi]

SELECT Artists_Cross.Artist, Artists_Cross.Role, Count(Artists_Cross.Role) AS CountOfRole
FROM Artists_Cross
GROUP BY Artists_Cross.Artist, Artists_Cross.Role
HAVING (((Artists_Cross.Artist)="xhonzi"))
ORDER BY Artists_Cross.Artist, Count(Artists_Cross.Role) DESC;

What I'm trying to do here is retrieve the most popular role for a particular artist. The output of this returns:

xhonzi Director 10
xhonzi Coffee Boy 9
xhonzi Writer 5

What I want it to return is the Director line and nothing more. What else do I need to do to my query?

Thanks,
xhonzi

 

[TheAceMan1]

How are ya xhonzi . . .

Try [blue]SELECT TOP 1 Artists_Cross.Artist,[/blue] . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[xhonzi]

Accurate and punctual as always, AceMan. Are you everyone's guardian angel or just mine?

xhonzi

 

[TheAceMan1]

xhonzi . . .

Lucky You! . . . I'm one of many!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886