Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2005
    Posts
    13

    Exclamation Unanswered: select max(count of rows) (was "SQL")

    Hi there,

    I have a problem in extracting data from db. My db is user_table with parameters like code1, code2, number_1, number_2, date_1 and date_2.

    I want to extract the number of users to code1 and code2.

    My coding are:

    SQL> select code1, count(code1) from user_table group by code1;

    Result is:

    CODE1 COUNT(CODE1)
    ------------ ----------------
    1234567890 5
    0987654321 2
    1472583690 6
    3692581470 1

    SQL> select max(code1) from user_table;

    MAX(CODE1)
    ------------
    1472583690

    When i did a max(code1), instead of giving me the maximum count(code1) which is 6, code1 - 3692581470 is given.

    Please advice.

    Thanks a million.

    Regards,

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't want the max(code1), you want the max(count of rows per code1):
    Code:
    select max(cnt) from
    ( select code1, count(code1) cnt
      from user_table
      group by code1
    );

  3. #3
    Join Date
    Jan 2005
    Posts
    13
    Quote Originally Posted by andrewst
    You don't want the max(code1), you want the max(count of rows per code1):
    Code:
    select max(cnt) from
    ( select code1, count(code1) cnt
      from user_table
      group by code1
    );
    Hi andrewst,

    Thank you for the reply. May i know what does max(cnt) means?

    Regards,

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    "cnt" is the name of a column in the inline view:

    select max(cnt) from
    ( select code1, count(code1) cnt
    from user_table
    group by code1
    );

  5. #5
    Join Date
    Jan 2005
    Posts
    13
    Quote Originally Posted by andrewst
    "cnt" is the name of a column in the inline view:

    select max(cnt) from
    ( select code1, count(code1) cnt
    from user_table
    group by code1
    );
    Thank you very much.

  6. #6
    Join Date
    Jan 2005
    Posts
    13
    Hi andrewst,

    I have another doubt. I would like to display both code1 and the maximum count at the same time. But i encounted this error prompt.

    select code1, max(cnt) from (select code1, count(code1) cnt from user_table group by code1)
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    Thank you.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's a little harder:
    Code:
    select code1, count(code1)
    from user_table
    group by code1
    having count(code1) =
    ( select max(cnt) from
      ( select code1, count(code1) cnt
        from user_table
        group by code1
      )
    );
    Or (since I believe you are using Oracle) you could use Oracle's RANK analytic function:
    Code:
    select code1, cnt from
    ( select code1, cnt, rank() over (order by cnt desc) rnk
      from
      ( select code1, count(code1) cnt
        from user_table
        group by code1
      )
    )
    where rnk=1;

  8. #8
    Join Date
    Jan 2005
    Posts
    13

    Thumbs up

    Quote Originally Posted by andrewst
    That's a little harder:
    Code:
    select code1, count(code1)
    from user_table
    group by code1
    having count(code1) =
    ( select max(cnt) from
      ( select code1, count(code1) cnt
        from user_table
        group by code1
      )
    );
    Or (since I believe you are using Oracle) you could use Oracle's RANK analytic function:
    Code:
    select code1, cnt from
    ( select code1, cnt, rank() over (order by cnt desc) rnk
      from
      ( select code1, count(code1) cnt
        from user_table
        group by code1
      )
    )
    where rnk=1;

    Wow. That's pretty tough.

    andrewst, you are a great help. I am able to get it.
    Thank you so much.

  9. #9
    Join Date
    Jan 2005
    Posts
    13
    Hi once again,

    I tried this code and it works fine. However, there is a slight problem. The maximum count of the code is empty (majority of the figure goes to this empty field).

    I would like to choose the 2nd maximum count which has a valid code number. Is there a possible way?

    Code
    ====
    select code1, count(code1)
    from user_table
    group by code1
    having count(code1) =
    ( select max(cnt) from
    ( select code1, count(code1) cnt
    from user_table
    group by code1
    )
    );

    Result
    =====
    CODE1 COUNT(CODE1)
    ------ ---------------
    (*empty*) 63232


    Or shall i include a { where code1 != ' ' } in the code as above.

    Please advice.

    Thank you very much.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use: "where code1 is not null"

  11. #11
    Join Date
    Jan 2005
    Posts
    13
    Quote Originally Posted by andrewst
    Use: "where code1 is not null"
    Hi andrewst,

    1st Doubt,

    I still encounter the same result irregardless whether i enter "where code1 is not null".

    SQL> select code1, count(code1) from subs_tbl where code1 is not null group by code1 having count(code1) = (select max(cnt) from (select code1, count(code1) cnt from users_table group by code1));

    CODE1 COUNT(CODE1)
    ------- --------------
    *empty* 65566

    2nd Doubt,

    Is there any possiblility where i can extract the 2nd maximum CODE1 and COUNT(CODE1).

    Thank you so much.

    Regards,

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, so it isn't a NULL in there then. That's nasty. You need to find out what is in there and then exclude that. e.g. if it is a single space then

    where code1 != ' '

    Sounds like your data needs cleaning up though: a "code" column should never be set to a value that is invisible (other than null of course)!

  13. #13
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    Quote Originally Posted by andrewst
    That's a little harder:
    Code:
    select code1, count(code1)
    from user_table
    group by code1
    having count(code1) =
    ( select max(cnt) from
      ( select code1, count(code1) cnt
        from user_table
        group by code1
      )
    );
    Or (since I believe you are using Oracle) you could use Oracle's RANK analytic function:
    Code:
    select code1, cnt from
    ( select code1, cnt, rank() over (order by cnt desc) rnk
      from
      ( select code1, count(code1) cnt
        from user_table
        group by code1
      )
    )
    where rnk=1;
    Hi,

    Why can't we do this way??

    SELECT TOP 1 code1, COUNT(code1) AS total_count
    FROM user_table
    GROUP BY code1
    ORDER BY total_count DESC

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jay82
    Hi,

    Why can't we do this way??

    SELECT TOP 1 code1, COUNT(code1) AS total_count
    FROM user_table
    GROUP BY code1
    ORDER BY total_count DESC
    Because Oracle doesn't support the SQL Server "TOP" syntax. The OP was using Oracle.

  15. #15
    Join Date
    Jun 2010
    Posts
    1
    Hi, thank you loads for the answers provided here, was of great use

Posting Permissions

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