3

Hi
i have a columm in the table and i want to select the most common item from the selected column. The table is set up publication:

  • id
  • Title
  • published

I want to beable to select the most recurring places where publications have been published. Is this possible to do?
Thanks in Advance
Dean

Dean
  • 8,668
  • 17
  • 57
  • 86

1 Answers1

3
select published, count(*) nbr
from table1
group by published
order by nbr desc
limit 1

You don't really need the count, but if you wanted confirmation that the choice seemed reasonable, you could use it. Also, you didn't specifically say whether you wanted ONLY the one, or wanted to see which was the most frequent, along with frequencies of the other records. Take off the limit 1 if you want to see all records.

MJB
  • 7,639
  • 2
  • 31
  • 41
  • Im after about 5 i would say its to allow the user to stop typing the publised everytime. – Dean Jun 23 '10 at 13:04
  • is it possible to add a where condition as publication has 3 different types which is a another column in the table called type? – Dean Jun 23 '10 at 13:34
  • Sure -- give some more info, either here or in new question. But based on what I know, I would go with "select published, count(*) nbr from table1 where type in ('type1', 'type2') group by published order by nbr desc limit 1" – MJB Jun 23 '10 at 14:07