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
--

Ambiguos of the ORDER BY

Previously I am using mssql2000
My sql is like this
SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol
This query is running fine
Then recently I upgrade my mssql server to mssql2005
The query above return an error said "ambiguos"
Then I change my query to
SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mytable.mycol
It works.
I am confuse of why the query can work fine on mssql2000 but cant work
on mssql2005
And why it can work after I change the
ORDER BY mycol
To
ORDER BY mytable.mycol
'
Can someone explain? Thank you!brian...@.gmail.com wrote:
> Previously I am using mssql2000
> My sql is like this
> SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol
> This query is running fine
> Then recently I upgrade my mssql server to mssql2005
> The query above return an error said "ambiguos"
> Then I change my query to
> SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mytable.mycol
> It works.
> I am confuse of why the query can work fine on mssql2000 but cant work
> on mssql2005
> And why it can work after I change the
> ORDER BY mycol
> To
> ORDER BY mytable.mycol
> '
> Can someone explain? Thank you!
SQL allows duplicate column names in query results. Usually the
duplicate names come from two different tables that are joined so if
you reference such a column without specifying a qualifying alias the
result is ambiguous because you may in fact be referring to either of
two different columns.
In your case the columns with duplicate names are in fact the same
column so it doesn't really matter which you refer to but SQL Server
2005 (rightly in my view) still insists that you qualify the name with
an alias. In SQL 2000 the ORDER BY didn't generate this error message,
which caused a lot of problems and confusion when duplicate names were
used.
Arguably the designers of SQL are to blame for allowing duplicate
column names at all. You would be well advised to modify your query to
return unique column names. Otherwise how will you refer to this data
in your client app? Relying on column positional indexes isn't good
practice.
SELECT mycol AS mycol1,
mycol AS mycol2
FROM mytable
WHERE active=1
ORDER BY mycol1;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--