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

    Unhappy Unanswered: subqueries in oracle

    while using subqueries in oracle how to restrict data using group by functions.
    example
    ROUTE_No DEPARTURE DRIVER
    ------------ -------------- ---------
    R51211 01-JUN-03 D90050
    R51218 01-JUN-03 D90057
    R51212 02-JUN-03 D90051
    R51219 02-JUN-03 D90058
    R51213 03-JUN-03 D90052
    R51220 03-JUN-03 D90059
    R51214 04-JUN-03 D90053
    R51221 04-JUN-03 D90050
    R51215 05-JUN-03 D90054
    R51222 05-JUN-03 D90051
    R51216 06-JUN-03 D90055
    R51217 07-JUN-03 D90056

    i want to retrieve the driver no who are assignmed most no of times only.in this case D90050 and D90051 who are assigned twice each.all other drivers are assigned once.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    select driver, count(*)
    from yourtable
    group by driver

    ... [having count(*) > 1]

    ... [order by count(*)]

    Hth
    Bill

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    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

Posting Permissions

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