An aggregate function for most/last frequent?

Is there any function to do something like:
SELECT MOSTFREQUENT(MyCol) FROM MyTable
so that if MyCol had the vals:
'A'
'A'
'B'
'A'
'C'
'C'
'B'
'A'
the result would be 'A'
as opposed to having to do something (roughly) like:
SELECT MyCol FROM MyTable WHERE MyCol =
(SELECT MyCol FROM (
SELECT TOP 1 MyCol, COUNT(1) FROM MyTable ORDER BY COUNT(1) DESC
))In t-SQL, try:
SELECT TOP 1 col
FROM tbl
GROUP BY col
ORDER BY COUNT( col ) DESC ;
Anith|||Try,
select top 1 with ties mycol
from mytable
group by mycolumn
order by count(*) desc
AMB
"Arthur Dent" wrote:

> Is there any function to do something like:
> SELECT MOSTFREQUENT(MyCol) FROM MyTable
> so that if MyCol had the vals:
> 'A'
> 'A'
> 'B'
> 'A'
> 'C'
> 'C'
> 'B'
> 'A'
> the result would be 'A'
> as opposed to having to do something (roughly) like:
> SELECT MyCol FROM MyTable WHERE MyCol =
> (SELECT MyCol FROM (
> SELECT TOP 1 MyCol, COUNT(1) FROM MyTable ORDER BY COUNT(1) DESC
> ))
>
>|||That couldn't really be a SQL aggregate function because it's a set rather
than a scalar value - there could be more than one value of equal frequency.
David Portas
SQL Server MVP
--|||That works, and is pretty clean enough...
Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoUQFAenFHA.3312@.tk2msftngp13.phx.gbl...
> In t-SQL, try:
> SELECT TOP 1 col
> FROM tbl
> GROUP BY col
> ORDER BY COUNT( col ) DESC ;
> --
> Anith
>|||Here is a version with the new CTE syntax:
WITH Histogram (mycol, tally)
AS (SELECT mycol, COUNT(*)
FROM Foobar
GROUP BY mycol)
SELECT mycol
FROM Histogram
WHERE tally = (SELECT MAX(tally) FROM Histogram);|||Very good point... i hadnt thought of that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:b_adnZ2dnZ1yEcKgnZ2dnQzGZ9-dnZ2dRVn-yZ2dnZ0@.giganews.com...
> That couldn't really be a SQL aggregate function because it's a set rather
> than a scalar value - there could be more than one value of equal
> frequency.
> --
> David Portas
> SQL Server MVP
> --
>|||Watch out for the solutions based on the TOP modifier because the results
may be non-deterministic. Unless you use the WITH TIES option or specify a
sort order that is unique you will just get some unpredictable "top" value
returned when there are duplicate values.
David Portas
SQL Server MVP
--