Using MAX to get one or two values in a count

Transact-SQL
1

Hi!

I have a inner join sql query that gets the count of number of groups that a member is apart of.

The result is

antal fornamn
3 Anna
2 Sara
3 Lisa
1 Peter

I need to get the rows that have the highest values it can be one row or two rows or more, if it has the same value. I tried to use a max but I dont know were to put it. I want the result to be in this case

antal fornamn
3 Anna
3 Lisa

the sql query is

SELECT COUNT(traningsgruppid) AS antal, medlem.fornamn
FROM traningsgrupprad
INNER JOIN medlem ON traningsgrupprad.medlemsid = medlem.medlemsid
GROUP BY medlem.fornamn

2 
SELECT TOP 1 WITH TIES
       COUNT(traningsgruppid) AS antal, medlem.fornamn
FROM traningsgrupprad
INNER JOIN medlem ON traningsgrupprad.medlemsid = medlem.medlemsid
GROUP BY medlem.fornamn
ORDER BY antal DESC
1 Like