how to find number of occurences of a value in a field?

[djc]

I have a recordset with say 3 fields: FirstName, LastName, Address. I want
to run a query to determine which LastName appears the most amount of times
and how many times that was. AND another query to find the opposite: which
one appears the least amount of times and how many times that was.

can I do this with sql?

note: there would be far to many unique 'LastName' s to write a seperate
select query on each one and compare the count.

anyone?

 

[Ken Snell]

Here is sample SQL that will show the LastName with most occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName) DESC;


Here is sample for the fewest occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName);

 

[djc]

Sweet! you rule!

Here is sample SQL that will show the LastName with most occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName) DESC;


Here is sample for the fewest occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName);