Sql Query Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Sql Query Help

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    Sql Query Help

    Hi all,

    I have table with the following columns and data:

    sequence_id organization_id
    ------------ -------------
    50 356
    50 356
    51 356
    52 356
    52 356
    52 357


    Here is my query, which distinct the result group by. (fine up to this point)

    select dist_sequence_id,
    destination_organization_id
    from po_req_dist_interface_all
    group by dist_sequence_id,
    destination_organization_id

    sequence_id organization_id
    ------------ -------------
    50 356
    51 356
    52 356
    52 357

    Now what I really need to see is, the count of the sequnce_id that appear more than one (based on the above result) and what are those.

    Some kind of inner and outer query can resolve the requirement. The above query is fine, but how I can extract the result that I need at the end?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    I'm not exactly sure what you are looking for but here are two options.

    Code:
    SELECT MAX(dist_sequence_id) dist_sequence_id,
           destination_organization_id
      FROM po_req_dist_interface_all
     GROUP BY destination_organization_id
     HAVNG COUNT(*) > 1;
    Code:
    SELECT COUNT(*) FROM (
    SELECT destination_organization_id
      FROM po_req_dist_interface_all
     GROUP BY destination_organization_id
     HAVNG COUNT(*) > 1);
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2001
    Posts
    334
    Hi thanks for the reply, but non of the meeting with my output.

    Main data store into table:
    sequence_id organization_id
    ------------ -------------
    50 356
    50 356
    51 356
    52 356
    52 356
    52 357

    Below is my query which group by and distinct the result, now from this out put need to know the count of the sequnce_id that appear more than one and what are those seq ids.

    select dist_sequence_id,
    destination_organization_id
    from po_req_dist_interface_all
    group by dist_sequence_id,
    destination_organization_id

    sequence_id organization_id
    ------------ -------------
    50 356
    51 356
    52 356
    52 357

    The output I am looking is :
    sequence_id organization_id
    ------------ -------------
    52 2

    Thanks.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    SELECT dist_squence_id, count(*) FROM
    (select dist_sequence_id,
     destination_organization_id
     from po_req_dist_interface_all
     group by dist_sequence_id,
     destination_organization_id)
    HAVING count(*) > 1
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Jul 2001
    Posts
    334
    Hi Dapi,

    I Just entered the below line before the last line,
    group by dist_sequence_id

    and it works perfect. Thanks for your help to resolve this issue.

    Appreciate.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Oops
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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


Click Here to Expand Forum to Full Width