Getting mor recursive value in a select

I need to get the value of the filed in mytable that appeears more often.
myfield1:
10
20
10
30
40
10
10
50
60
70
20
30
10
I need a query tha get me 10.
ThanksAm Wed, 5 Oct 2005 07:39:06 -0700 schrieb checcouno:

> I need to get the value of the filed in mytable that appeears more often.
> myfield1:
> 10
> 20
> 10
> 30
> 40
> 10
> 10
> 50
> 60
> 70
> 20
> 30
> 10
> I need a query tha get me 10.
> Thanks
select myfield1 from mytable group by myfield1 having count(*) > 1
bye,
helmut|||SELECT col
FROM tbl
GROUP BY col
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM tbl
GROUP BY col) ;
Don't forget that there could be more than one value that is the most
fequent unless you have business rules to prevent this.
David Portas
SQL Server MVP
--
"checcouno" wrote:

> I need to get the value of the filed in mytable that appeears more often.
> myfield1:
> 10
> 20
> 10
> 30
> 40
> 10
> 10
> 50
> 60
> 70
> 20
> 30
> 10
> I need a query tha get me 10.
> Thanks
>|||Isn't best
select top 1 myfield1 from mytable group by myfield1 order by count(*)
?
Thanks
"helmut woess" wrote:

> Am Wed, 5 Oct 2005 07:39:06 -0700 schrieb checcouno:
>
> select myfield1 from mytable group by myfield1 having count(*) > 1
> bye,
> helmut
>|||...order by count(*) DESC (of course!)
"David Portas" wrote:
> SELECT col
> FROM tbl
> GROUP BY col
> HAVING COUNT(*) >= ALL
> (SELECT COUNT(*)
> FROM tbl
> GROUP BY col) ;
> Don't forget that there could be more than one value that is the most
> fequent unless you have business rules to prevent this.
> --
> David Portas
> SQL Server MVP
> --
> "checcouno" wrote:
>|||Which query is more quick?
SELECT col
FROM tbl
GROUP BY col
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM tbl
GROUP BY col) ;
or
select top 1 myfield1 from mytable group by myfield1 order by count(*) DESC
?
I need only one value, i don't mind if there is more than one value
Thanks|||The 2nd one will be quicker, as the first is doing two scans.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:55864B3A-1DF3-4204-B03C-36618B0F9AA4@.microsoft.com...
> Which query is more quick?
> SELECT col
> FROM tbl
> GROUP BY col
> HAVING COUNT(*) >= ALL
> (SELECT COUNT(*)
> FROM tbl
> GROUP BY col) ;
> or
> select top 1 myfield1 from mytable group by myfield1 order by count(*)
DESC
> ?
> I need only one value, i don't mind if there is more than one value
> Thanks
>
>
>|||> Isn't best
> select top 1 myfield1 from mytable group by myfield1 order by count(*)
> ?
No, because the result is undefined if you have ties. It you use TOP then
use the WITH TIES option.
David Portas
SQL Server MVP
--
"checcouno" wrote:
> Isn't best
> select top 1 myfield1 from mytable group by myfield1 order by count(*)
> ?
> Thanks
>
> "helmut woess" wrote:
>|||> Which query is more quick?
They give different results so that doesn't seem like a sensible comparison.
Regardless of that, performance is something you should test on your own
system with your data.

> I need only one value, i don't mind if there is more than one value
That seems like a contradiction but I think you mean you want any ONE value
rather than more than one. That's not necessarily a problem but it makes
sense to ensure that your query returns a well defined result rather than
"any one row at random". Random results are hard to test and tend to look
like bugs. Amend your specification accordingly if possible - even if that's
just to specify the minimum of the possible values or the first one when the
y
are sorted in some fixed order.
David Portas
SQL Server MVP
--
"checcouno" wrote:

> Which query is more quick?
> SELECT col
> FROM tbl
> GROUP BY col
> HAVING COUNT(*) >= ALL
> (SELECT COUNT(*)
> FROM tbl
> GROUP BY col) ;
> or
> select top 1 myfield1 from mytable group by myfield1 order by count(*) DES
C
> ?
> I need only one value, i don't mind if there is more than one value
> Thanks
>
>
>