how to select most frequent item from the table using mysql query?

Question

i create a table for transaction table using mysql... i need to retrieve the frequent transaction data from that table... how find the frequent data from the table help me!

Answer 1

try this:

Select Top 1 Item
From 
   (Select Item, Count(*) Frequency
    From Table
    Group By Item
    Order By Count(*) Desc) Z

which returns only one record, or...

Select Item From
   (Select Item, Count(*) Frequency
    From Table
    Group By Item) Z
Where Z.Frequwncy = 
  (Select Max(Frequency) From Z)

which will return all records with that maximum frequency (count).
Add whatever predicates or other output columns you need to furthur customize the sql...