Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: Join and distinct on the same table?

    I have a view with the following structure:

    EMPLID,POSNO,MGRPOSNO

    With POSNO being the Position Number and MGRPOSNO being the Manager Position Number.

    I am trying to do a join however I am running into an issue where there are multiple Position Numbers for a single role, that many employees (and thus managers) report to.

    I am trying a select statement like this:

    select a.emplid, a.posno, a.mgrposno, b.emplid as managerid
    from empl_vw a, (select posno, emplid from empl_vw z where z.status='A') b
    where a.mgrposno = b.posno(+);

    however this returns employees many times if the managers position number has many managers in that position.

    How should I do a distinct to get the first person in a managers position and return that as part of the join?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How should I do a distinct to get the first person in a managers position and return that as part of the join?
    rows in a table are like balls in a basket.

    Which ball in a basket is the first ball?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    rows in a table are like balls in a basket.
    Unless you're sorting or grouping them. But, I'd agree that it's best (in terms of writing reliable, maintainable code) to think of them as balls in a basket.

    How should I do a distinct to get the first person in a managers position and return that as part of the join?
    You need to break the problem down more. There are a couple of ways to do it, but my strategy would be to use a CTE (a WITH statement) to distinguish which is the first person (find some identifier as needed), and then join that back in as needed. Some reference on CTEs.

  4. #4
    Join Date
    Jun 2010
    Posts
    1

    Hierarchical Queries?

    I didn't fully understand what you are looking for but perhaps a Hierarchical Queries may solve your issue:

    Hierarchical Queries

Posting Permissions

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