Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    47

    Angry Unanswered: hi continued to the last doubt...

    hi billm thanx but there is a small confusion i dont want to give count(*) > 1 in the having clause.Instead i want to implement max(count(*)).
    Kindly clarify.
    That is i dont want to fetch records > 1 in having.i want to fetch the max count record alone.
    please help me.
    ur previous suggestion was very useful.

    bye

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Oops! reposted on the correct thread....

    Something sorely missed in Oracle is a way of only retrieving the first row or [n] rows easily. The common workaround is...

    select *
    from (
    select driver, count(*)
    from yourtable
    group by driver
    order by count(*) desc
    )
    where rownum = 1

    You could potentially run a subquery for the max( count(*) ) but that would have the potential to return more than one row. The above should do what you need.

    If two drivers have the same total, it will be arbitrary as to which is returned.

    Hth
    Bill

  3. #3
    Join Date
    May 2003
    Posts
    47

    Unhappy subquery continued

    hi,

    The last solution you gave just returns only one value if two drivers match the same criteria.
    But i also noticed that u posted that it will return an arbitary row .
    But i want all rows which match the criteria.

    Prolonging the question.
    sorry.
    help me.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    This will do it...

    select driver, count(*)
    from test
    group by driver
    having count(*) = (
    select max( totrecs )
    from (
    select driver,
    count(*) as totrecs
    from test
    group by driver
    )
    )

    but I'm sure there are more efficient ways. I would consider having a trigger to update a total column for your driver table to have this value immediately accessible.

    Hth
    Bill

  5. #5
    Join Date
    May 2003
    Posts
    47

    Re: hi continued to the last doubt...

    Hi,
    Its working perfectly.Thanx a lot for your valuable help.
    Regards
    sundar.

Posting Permissions

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