Determining the most and least values in a column

# Thread: Determining the most and least values in a column

1. Junior Member
Join Date
Dec 1969
Posts
14

## Determining the most and least values in a column

I have a column with many names such as tony, john , mike and some of them appear a few times. Some of the names I do not even know and its a large column with 1000 names. Say tony, tony tony, james etc. I need to find out which name occurs the most and which the least. Any idea on how to write the SQL statement ? Any special function to use ? Cannot use count since we do not know the names and in any event, we only interested in the most and least. Thanks and rgds

2. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Determining the most and least values in a col

SELECT MIN(A.theCount), A.theName FROM<BR> (SELECT COUNT(*) as theCount, theName <BR> FROM tblNames<BR> GROUP BY theName) as A<BR><BR>replace MIN with MAX for the maximum values<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
140

## Will not work

Hi dutch <BR><BR>MIN(A.theCount), A.theName <BR><BR>cant go togeather as the SQL asks for grouping function for theName. If I group it all the vaues will come..<BR>

4. Senior Member
Join Date
Dec 1969
Posts
140

## This will work

SELECT A.theName FROM <BR> (SELECT COUNT(*) as theCount, theName <BR> FROM tblNames <BR> GROUP BY theName) as A where <BR>(A.theCount = (SELECT MIN(B.theCount) From (SELECT COUNT(*) as theCount, theName <BR> FROM tblNames <BR> GROUP BY theName) as B))<BR><BR>Is there any othere way Dutch.

5. Senior Member
Join Date
Dec 1969
Posts
7,686

## sigh

of course this doesn&#039;t work. This happens when you try to give a fast reply....<BR><BR><BR>this will work:<BR><BR>SELECT TOP 1 A.theCount, A.theName FROM<BR> (SELECT COUNT(*) as theCount, theName FROM tblNames GROUP By theName) as A<BR>ORDER BY theCount DESC<BR><BR>this will give the maximum number, when you replace DESC with ASC it will give the minimum.<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•