Sort by number of unique entries in a datafield

  • Thread starter Thread starter Mycroft
  • Start date Start date
M

Mycroft

I have multi-column data where I'd like to sort on the *number* of
unique elements in a given column, generally to list the most common
entries first.

My first columns look something like this:

smith ...
smith ...
olson ...
doe ...
doe ...
doe ...

I'd like the query to sort on the unique elements by most numerous
first such as:

doe ...
doe ...
doe ...
smith ...
smith ...
olson ...

There is of course a lot more data and it is absolutely huge. But it
is requested to be in this order - so I can tackle the largest number
of elements first.

I thought perhaps create a data column that has the total number:

smith ... 2
smith ... 2
olson ... 1
doe ... 3
doe ... 3
doe ... 3

Which would get the job done (via sort) but can't even get that
working. Plus its not very elegant.

Thanks.
 
Mycroft said:
I have multi-column data where I'd like to sort on the *number* of
unique elements in a given column, generally to list the most common
entries first.

My first columns look something like this:

smith ...
smith ...
olson ...
doe ...
doe ...
doe ...

I'd like the query to sort on the unique elements by most numerous
first such as:

doe ...
doe ...
doe ...
smith ...
smith ...
olson ...

There is of course a lot more data and it is absolutely huge. But it
is requested to be in this order - so I can tackle the largest number
of elements first.

I thought perhaps create a data column that has the total number:

smith ... 2
smith ... 2
olson ... 1
doe ... 3
doe ... 3
doe ... 3

Which would get the job done (via sort) but can't even get that
working. Plus its not very elegant.

Thanks.
Click to expand...

Assuming SQL 2005 or later:

;WITH q AS (
select datafield,count(*) Total from yourtable group by datafield)
select datafield
from yourtable t join q on t.datafield=q.datafield
order by Total,datafield

Earlier versions of SQL:

select datafield
from yourtable t join (
select datafield,count(*) Total from yourtable group by datafield)
q on t.datafield=q.datafield
order by Total,datafield
 
One solution (if you need to be able to UPDATE the results) is to create an
additional table and populate it using a totals query.

INSERT INTO tblNameCount(NameField, TheCount)
SELECT NameField, Count(Namefield) as TheCount
FROM YourTable
GROUP BY NameField

Then use that table in a query
SELECT [YourTable].Namefield, ...
FROM [YourTable] INNER JOIN tblNameCount
ON [YourTable].NameField = tblNameCount.NameField
ORDER BY tblNameCount.TheCount, [YourTable].NameField

If you don't need to update the results then just use an aggregate query to
get the count. Either a saved query or a sub-query (the SQL of which would be
something like the following.

qryNameCount would be:
SELECT NameField, Count(Namefield) as TheCount
FROM YourTable
GROUP BY NameField

Your query would be:
SELECT [YourTable].Namefield, ...
FROM [YourTable] INNER JOIN QryNameCount
ON [YourTable].NameField = tblNameCount.NameField
ORDER BY tblNameCount.TheCount, [YourTable].NameField

John Spencer
Access MVP 2002-2005, 2007-2011
 
Back
Top