Results 1 to 5 of 5

Thread: UNION question

  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: UNION question

    All,

    I have a question on unions in 9i.

    Lets say I have a query like:

    SELECT name,rank from personel
    UNION
    SELECT name,rank from old_personel


    name is a string, rank is a string.

    What I want to do is return the name and the MAX rank only.

    If the tables looks like:

    personel.name personel.rank
    John Smith Colonel

    old_personel.name old_personel.rank
    John Smith Private


    I should get a result set something like:

    1 John Smith Colonel
    2 John Smith Private

    I want to get:
    1 John Smith Colonel

    As a second question, if rank is an arbitrary list, how would I go about letting oracle know which one is the max ranking? A temporary table? or an array?

    This is being developed in a PL/SQL function, so I can use other methods if there is not a standard SQL query method of going about it.

    Thanks again,
    --james

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The idea is to manually order ranks (ORDER BY clause in RANK), as you don't have any table which would hold them; kind of a normalized situation would be like this:
    Code:
    CREATE TABLE ranks 
      (rnk NUMBER(2) PRIMARY KEY, 
       rank_name VARCHAR2(20));
    
    CREATE TABLE personel 
      (ID NUMBER, 
       pers_name VARCHAR2(20), 
       rnk_date DATE,
       rnk NUMBER(2) CONSTRAINT fk_pers_rnk REFERENCES ranks (rnk),
       CONSTRAINT pk_pers PRIMARY KEY (ID, rnk_date));
    
    SQL> select * From ranks order by rnk;
    
           RNK RANK_NAME
    ---------- --------------------
             1 General
             2 Colonel
             3 Private
    
    SQL> select * From personel order by id, rnk;
    
            ID PERS_NAME            RNK_DATE        RNK
    ---------- -------------------- -------- ----------
          1000 John Smith           05.09.04          2
          1000 John Smith           10.07.00          3
    But, as you don't have is (at least it seems so), your query might look like this:
    Code:
    SELECT rnk, NAME, pers_rank
      FROM (SELECT NAME, pers_rank,
                   RANK () OVER (ORDER BY DECODE (pers_rank,
                                                  'Private', 3,
                                                  'Colonel', 2,
                                                  'General', 1
                                                 )) rnk
              FROM (SELECT 'John Smith' NAME, 'Private' pers_rank
                      FROM DUAL PERSONEL
                    UNION
                    SELECT 'John Smith' NAME, 'Colonel' pers_rank
                      FROM DUAL PERSONEL
                    UNION
                    SELECT 'John Smith' NAME, 'General' pers_rank
                      FROM DUAL PERSONEL))
     WHERE rnk = 1;

  3. #3
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by Littlefoot
    The idea is to manually order ranks (ORDER BY clause in RANK), as you don't have any table which would hold them; kind of a normalized situation would be like this:
    Thanks for the response. I didn't know you could manually set the ranking. I coudl easily add a table to hold this data. I'll give that a try and see how it comes out. Thanks!

    --james

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Nice one LF.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you

Posting Permissions

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