Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: Problem of max(count(*))

    Can someone tell me why the following sql statement cannot retrieve the
    maximum count of the record:

    SELECT col, max(count(*)) FROM table
    GROUP BY col

    And also, can I use one sql statement to retrieve the maximum count of the
    record? If not, how?

    Thank you more!!

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    You cannot give a function as argument for max().
    Try this

    SELECT col, max(colcount) from
    (SELECT col, count(*) as colcount
    FROM TABLE
    GROUP BY col) AS aa
    GROUP BY col
    Rodney Krick

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    Originally posted by RKrick
    You cannot give a function as argument for max().
    Try this

    SELECT col, max(colcount) from
    (SELECT col, count(*) as colcount
    FROM TABLE
    GROUP BY col) AS aa
    GROUP BY col
    Could you explain in details why we cannot give a function as argument for max()

  4. #4
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    It makes no sense. You can give a scalar function as an argument for a column function, but not a column function as argument for a column function (look at SQL Reference).
    Rodney Krick

Posting Permissions

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