Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Question Unanswered: getting value for max # of occurrences on a table

    I would like to know if there is a way to get the value of a column where that value occurs the most times in a table.

    I have this query that gets the value and the number of times it occurs. What I want is just the value that occurs the most times:

    select COL_NAME, count(COL_NAME) from config_db..TABLE_NAME
    group by COL_NAME

    What I need is something like:

    select COL_NAME from config_db..TABLE_NAME having max(count(COL_NAME))

    but of course this does not work.

    The closest I can get is:

    select COL_NAME from config_db..TABLE_NAME group by COL_NAME
    having count(COL_NAME)= (select max(count(COL_NAME)) from config_db..TABLE_NAME)

    This returns null. If I substitute the actual numeric result of the (select max(...)) then the query works.

    Thank you!
    Last edited by mellowmorton; 12-14-06 at 15:27.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select top 1 COL_NAME, count(*)
    from config_db..TABLE_NAME
    group by COL_NAME
    order by 2 desc

  3. #3
    Join Date
    Dec 2006
    Posts
    3

    Cool Solution!

    Thanks to pdreyer for getting me on the right track.

    I was not able to get the top command to work with Sybase (v. 12.5), but the approach gave me the idea to do this:

    set rowcount 1
    select COL_NAME
    from config_db..TABLE_NAME
    group by COL_NAME
    order by count(*) desc

    This allows me to retrieve the COL_NAME only so that I can use it in an insert/update. Thanks for your help pdreyer!

  4. #4
    Join Date
    Nov 2006
    Posts
    13
    select id,max(count(id)) from #w
    group by id
    having max(count(id))=count(id)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •