Results 1 to 12 of 12

Thread: Help with sql

  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: Help with sql

    I would appreciate if you could help me write a sql for the problem. I am not sure if I explain the problem well.

    I have applications for dorm room and each application may have upto 3 roommate preference (some may have 2)
    So there must be 3 pk_appl_no rows with same roommate preferences in different positions

    sample data:
    PK_A_NO RM_1 Rm_2 RM_3
    101 1111 2222 3333
    102 2222 3333 1111
    103 8080 6060 7070
    104 3333 2222 1111
    108 1010 2020
    110 2020 1010
    122 4040 5050 9090

    The query should return:
    PK_A_NO RM_1 RM_2 RM_3
    101 1111 2222 3333
    108 1010 2020

    Thanks in advance,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by parksandbeaches
    I am not sure if I explain the problem well.
    well, yes, sorry, you didn't


    Quote Originally Posted by parksandbeaches
    The query should return:
    why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    4
    PK_A_NO is the primary key for application number.
    applicants 101, 102, and 104 want to be roommates togethers so query should return 1 row for the 3 rows. Similarly for applicant 108 and 110 there should be 1 record returned.

    HTML Code:
    <pre>sample data:
    PK_A_NO   RM_1     Rm_2      RM_3
    101          1111      2222      3333
    102          2222      3333      1111
    103          8080      6060      7070
    104          3333      2222      1111
    108          1010      2020
    110          2020      1010
    122          4040      5050      9090
    
    The query should return:
    PK_A_NO   RM_1      RM_2     RM_3
    101          1111       2222      3333
    108          1010       2020     null
    103          8080      6060      7070
    122          4040      5050      9090
    
    </pre>
    Am I clear this time?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i understand now, thanks

    i am not sure what the effect of the "empty" 3rd spot will be, but the following will work if all three are filled in:
    Code:
    select min(PK_A_NO)
         , count(*) as applications 
         , RM_foo
         , RM_bar
         , RM_qux
      from ( select PK_A_NO
                  , least(RM_1,RM_2,RM_3) as RM_foo
                  , greatest(RM_1,RM_2,RM_3) as RM_bar
                  , case when RM_1 < least(RM_2,RM-3)
                         then least(RM_2,RM-3)
                         when RM_2 < least(RM_1,RM-3)
                         then least(RM_1,RM-3)
                         else least(RM_1,RM-2) end as RM_qux
               from sampletable
           ) as ordered
    group
        by RM_foo
         , RM_bar
         , RM_qux
    check the number of applications in the 2nd column of the result
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Another idea, but it's a first shot :

    Code:
    SELECT *
    FROM
    (
        (
            SELECT RP.PK_A_NO, RM_1, RM_2, RM_3
            FROM
            (
                SELECT RM_LIST, MIN(PK_A_NO) PK_A_NO
                FROM
                (
                    SELECT PK_A_NO, GROUP_CONCAT(RM ORDER BY RM SEPARATOR ',') RM_LIST
                    FROM
                    (
                        SELECT PK_A_NO, RM_1 RM
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_2
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_3
                        FROM Room_Preferences
                    ) V1
                    GROUP BY PK_A_NO
                ) V2
                GROUP BY RM_LIST
                HAVING COUNT(*) > 1
            ) V3, Room_Preferences RP
            WHERE RP.PK_A_NO = V3.PK_A_NO
        )
        UNION ALL
        (
            SELECT RP.PK_A_NO, RM_1, RM_2, RM_3
            FROM
            (
                SELECT RM_LIST, MIN(PK_A_NO) PK_A_NO
                FROM
                (
                    SELECT PK_A_NO, GROUP_CONCAT(RM ORDER BY RM SEPARATOR ',') RM_LIST
                    FROM
                    (
                        SELECT PK_A_NO, RM_1 RM
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_2
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_3
                        FROM Room_Preferences
                    ) V1
                    GROUP BY PK_A_NO
                ) V2
                GROUP BY RM_LIST
                HAVING COUNT(*) = 1
            ) V3, Room_Preferences RP
            WHERE RP.PK_A_NO = V3.PK_A_NO
        )
    ) V4
    ORDER BY PK_A_NO;
    Code:
    +---------+------+------+------+
    | PK_A_NO | RM_1 | RM_2 | RM_3 |
    +---------+------+------+------+
    |     101 | 1111 | 2222 | 3333 |
    |     103 | 8080 | 6060 | 7070 |
    |     108 | 1010 | 2020 | NULL |
    |     122 | 4040 | 5050 | 9090 |
    +---------+------+------+------+
    4 rows in set (0.03 sec)
    
    mysql>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rbaraer, you have made the same mistake i did -- GROUP_CONCAT() won't work in oracle, and i don't think LEAST() and GREATEST() will either

    however, LEAST() and GREATEST() are easy to substitute with CASE expressions, very much like the CASE expression i used in the 3rd column of my subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2008
    Posts
    4
    Thanks guys, I appreciate it, but I am still getting duplicates. Any ideas?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by parksandbeaches
    Thanks guys, I appreciate it, but I am still getting duplicates. Any ideas?
    could you show the query that you ran, pls?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by r937
    rbaraer, you have made the same mistake i did -- GROUP_CONCAT() won't work in oracle, and i don't think LEAST() and GREATEST() will either

    however, LEAST() and GREATEST() are easy to substitute with CASE expressions, very much like the CASE expression i used in the 3rd column of my subquery

    I'm gonna work with MySQL now much more than with Oracle, so I was sure I was in the MySQL forum Thanks Rudy.

    So for Oracle >= 10g the following works.

    Some preliminary work to simulate MySQL's GROUP_CONCAT:
    Code:
    CREATE OR REPLACE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
    
    CREATE OR REPLACE FUNCTION TableToString(
        strtblTable IN VARCHAR2_TABLE,
        strSeparator IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 AS
        
    strResult VARCHAR2(4000);
        
    BEGIN
    
        FOR i IN 1..strtblTable.COUNT LOOP
            IF (i = 1) THEN
                strResult := strtblTable(i);
            ELSE
                strResult := strResult||strSeparator||strtblTable(i);
            END IF;
        END LOOP;
        
        RETURN strResult;
    
    END TableToString;
    /
    And the SQL :
    Code:
    SELECT *
    FROM
    (
        (
            SELECT RP.PK_A_NO, RM_1, RM_2, RM_3
            FROM
            (
                SELECT RM_LIST, MIN(PK_A_NO) PK_A_NO
                FROM
                (
                    SELECT PK_A_NO, TableToString(CAST(COLLECT(TO_CHAR(RM) ORDER BY RM) AS VARCHAR2_TABLE)) RM_LIST
                    FROM
                    (
                        SELECT PK_A_NO, RM_1 RM
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_2
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_3
                        FROM Room_Preferences
                    ) V1
                    GROUP BY PK_A_NO
                ) V2
                GROUP BY RM_LIST
                HAVING COUNT(*) > 1
            ) V3, Room_Preferences RP
            WHERE RP.PK_A_NO = V3.PK_A_NO
        )
        UNION ALL
        (
            SELECT RP.PK_A_NO, RM_1, RM_2, RM_3
            FROM
            (
                SELECT RM_LIST, MIN(PK_A_NO) PK_A_NO
                FROM
                (
                    SELECT PK_A_NO, TableToString(CAST(COLLECT(TO_CHAR(RM) ORDER BY RM) AS VARCHAR2_TABLE)) RM_LIST
                    FROM
                    (
                        SELECT PK_A_NO, RM_1 RM
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_2
                        FROM Room_Preferences
                        UNION ALL
                        SELECT PK_A_NO, RM_3
                        FROM Room_Preferences
                    ) V1
                    GROUP BY PK_A_NO
                ) V2
                GROUP BY RM_LIST
                HAVING COUNT(*) = 1
            ) V3, Room_Preferences RP
            WHERE RP.PK_A_NO = V3.PK_A_NO
        )
    ) V4
    ORDER BY PK_A_NO;
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    More of the same. I assumed rooms were at least/most 4 numbers of length.
    Code:
    SQL> create or replace function roomsort( p_rooms in varchar2,
      2                                       p_delim in varchar2 default ',' )
      3  return varchar2
      4  is
      5     type    MyTableType is table of pls_integer index by varchar2(4);
      6     l_col   MyTableType;
      7     l_ele   varchar2(4);
      8     l_res   varchar2(500);
      9     l_rooms varchar2(500) default p_rooms || p_delim;
     10     x       number;
     11  begin
     12     loop
     13             exit when l_rooms is null;
     14             x := instr( l_rooms, p_delim );
     15             l_ele := nvl( substr( l_rooms, 1, x - 1 ), '0000' );
     16             --
     17             if l_col.exists( l_ele )
     18             then
     19                    l_col( l_ele ) := l_col( l_ele ) + 1;
     20             else
     21                    l_col( l_ele ) := 1;
     22             end if;
     23             --
     24             l_rooms := substr( l_rooms, x + 1 );
     25     end loop;
     26     for i in l_col.first .. l_col.last
     27     loop
     28             if l_col.exists( i )
     29             then
     30                     l_res := l_res || i;
     31             end if;
     32     end loop;
     33     return l_res;
     34  end;
     35  /
    
    Function created.
    
    SQL>
    SQL> select pk_a_no, rm_1, rm_2, rm_3
      2    from (
      3  select t.*,
      4         min( pk_a_no ) over( partition
      5                         by roomsort( rm_1 || ',' || rm_2 || ',' || rm_3 ) ) roomsort
      6    from t
      7         )
      8   where pk_a_no = roomsort
      9  /
    
       PK_A_NO       RM_1       RM_2       RM_3
    ---------- ---------- ---------- ----------
           108       1010       2020
           101       1111       2222       3333
           122       4040       5050       9090
           103       8080       6060       7070
    
    SQL>

  11. #11
    Join Date
    Mar 2008
    Posts
    4
    r937,
    I ran the following and am still getting duplicates. I actually have 11 roommate preferences (each application may have upto 11 roommates). I thought I could scale the query and it will be simple to discuss in this forum.

    select min(appl_no) MIN
    , count(*) as applications
    , RM_foo
    , RM_bar
    , RM_qux
    from ( select appl_no
    , least(roommate_sid_1,roommate_sid_2,roommate_sid_3 ) as RM_foo
    , greatest(roommate_sid_1,roommate_sid_2,roommate_si d_3) as RM_bar
    , case when roommate_sid_1 < least(roommate_sid_2,roommate_sid_3)
    then least(roommate_sid_2,roommate_sid_3)
    when roommate_sid_2 < least(roommate_sid_1,roommate_sid_3)
    then least(roommate_sid_1,roommate_sid_3)
    else least(roommate_sid_1,roommate_sid_2) end as RM_qux
    from zz_t_appl_rm
    ) ordered
    group
    by RM_foo
    , RM_bar
    , RM_qux;

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you saying that oracle supports the LEAST and GREATEST functions? that's great news!!

    as for your clever trick of disguising 11 columns as three, well, you get what you pay for, my solution works only on three, and i just don't feel like helping any more

    good luck!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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