> Hi All
>  I need a query help.
>
>  How to display the record(only record) that has maximum occurrence of
> a particular
> value in a column?
> Say, I have a Table called Table1 with columns Col1, Col2, Col3 and
> Col4 and Col5. I want to display the record details that occurs maximum
> number
> of times, when I run a query. Assuming all the records will be
> displayed
> by the selection criteria. But how would I limit only the first record
> that has the same col3 value. (That is 30 in this case.)
>
>  SELECT top 1 col1, col2, col3, col4 FROM Table1
> WHERE (col1 like 'Matching%')
> group by col1,col2,col3, col4 order by COl1
>
> Insert into Table1(col1, col2,col3, col4, col5)values
> (1,2,30,4,'MatchingValue')
> Insert into Table1(col1, col2,col3, col4, col5)values
> (2,22,30,40,'MatchingValue')
> Insert into Table1(col1, col2,col3, col4, col5)values
> (3,12,13,41,'MatchingValue')
> Insert into Table1(col1, col2,col3, col4, col5)values
> (2,22,23,44,'MatchingValue')
>
> TIA.

> if you have a table like htis
> tbl
> class       id       grade
> math       1           90
> math       2           80
> scc         1           89
> scc         2           90
>
> select class, ID, max(grade)
> from tbl
> group by class,id
>
> will give you this
> math 1 90
> scc   2 90
>
>
>
>
>
>
> Ami wrote:
> > Hi All
> >  I need a query help.
> >
> >  How to display the record(only record) that has maximum occurrence of
> > a particular
> > value in a column?
> > Say, I have a Table called Table1 with columns Col1, Col2, Col3 and
> > Col4 and Col5. I want to display the record details that occurs maximum
> > number
> > of times, when I run a query. Assuming all the records will be
> > displayed
> > by the selection criteria. But how would I limit only the first record
> > that has the same col3 value. (That is 30 in this case.)
> >
> >  SELECT top 1 col1, col2, col3, col4 FROM Table1
> > WHERE (col1 like 'Matching%')
> > group by col1,col2,col3, col4 order by COl1
> >
> > Insert into Table1(col1, col2,col3, col4, col5)values
> > (1,2,30,4,'MatchingValue')
> > Insert into Table1(col1, col2,col3, col4, col5)values
> > (2,22,30,40,'MatchingValue')
> > Insert into Table1(col1, col2,col3, col4, col5)values
> > (3,12,13,41,'MatchingValue')
> > Insert into Table1(col1, col2,col3, col4, col5)values
> > (2,22,23,44,'MatchingValue')
> >
> > TIA.

> Ami wrote:
> > Thank you, but I am not interested in finding the max value, but
> > interested in the maximum occurrence of the value. Assuming
> > 90 being a value, this occurs twice.
> > Sorry for the confusion.
>
> I think you would need to normalize your data first (i.e. put your
> columns into rows in a temp table or something), then the query to
> identify the most frequently occurring values should be easier.
>
> Chris

> Ami wrote:
> > Yes, I am interested in knowing that. Could someone help. Thank you.
>
> Please provide an expected result based on your sample data. I'm still
> not sure exactly what your requirements are.