Selecting the record which occur most

[Anon]

Hi,

If I have a field which has a number value I would like to use an sql statement to select the "number" which occurs most within the table.

eg. 7 7 7 7 8 8 8 9 9 9 might be spread accross 10 records but I want the number 7 to be returned.

[avickers]

This isn't a "proper" solution, but you can use it as a workaround for now.

On the data you suggested, the query:

select
myfield,
counts=count(myfield)
from
mytable
group by
myfield
order by
count(myfield) desc

will return the records:
myfield counts
7 4
8 3
9 3

The most popular number will always be the first row, so you only need to use _result($dbid,0,0) to get the result you desire.

Upsides to this approach: You can check for two or more "most popular numbers".
Downsides: You're getting lots of useless information.

If I find a way to restrict it to the single row, I'll post.

Please note - this works in MS SQL Server / Sybase. For other databases you may need to adjust the dialect.

[[deleted]]

In MySQL you can simply add "LIMIT 1" to get only the first record of the resultset.

[Anon]

Thats not the problem. limit will just restrict the number of returns, I want to count the amount of times a number occurs within a field set and the largest number to be returned to me.

[trellie]

GROUP BY and ORDER by will do that...

[trellie]

bloed, nagels...
nevermind... no comment 🙂

[trellie]

ow... damn... wrong thread... LOL 🙂

[[deleted]]

been drinking again?

[[deleted]]

Ya, that's kind of the solution that Ade Vickers gave you, but he said he didn't have a way of limiting the output to just the record you want. His query will return a list of how often each value occurs, starting with the one that occurs most often.
So, adding a "LIMIT 1" means only the first result is returned, and that's the one you want.

[avickers]

The only trouble with LIMIT is it's not the same across all SQL variants (don't get me wrong - it's a cool solution to the problem).

Using MSSQL/Sybase, I'd call "set rowcount 1". I'm not sure what the pgSQL version is, I'd need to look at the manual.

However - while it IS a solution, and a cool one, for completeness sake I'd like to find one that's 100% SQL. And it's bugging me now, so I will fix it - even if I'm into multiple-nested queries :-)

[[deleted]]

I don't really understand the problem anymore..
You have a query that returns the data you like, it just returns a lot of other data too.

Why don't you just take the record you need and forget about the rest?

[avickers]

Well....if the query returns 100,000 rows (very possible in a big database) you've got a massive overhead in that big resultset - if you can cut down the number of rows to 1 before it gets returned to php, you've (probably) saved a bag of time.

Of course, if it takes longer for the SQL to run than for php to process the result, then you'd be right to leave it as it is.

Cheers,
Ade.