Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!ucla-cs!elroy.jpl.nasa.gov!sdd.hp.com!spool.mu.edu!uunet!fernwood!oracle!news
From: mwang@oracle.com (Michael Wang)
Newsgroups: comp.databases
Subject: Re: SQL Problem
Message-ID: <1991Feb13.151919.23703@oracle.com>
Date: 13 Feb 91 15:19:19 GMT
References: <1991Feb12.023644.14504@i88.isc.com>
Sender: news@oracle.com
Distribution: usa
Organization: Oracle Corporation, Redwood Shores, CA
Lines: 152

In article <1991Feb12.023644.14504@i88.isc.com>
egn@i88.isc.com (E. G. Nadhan) writes:
>Looking for possible solutions to the following problem:
>
>The table from which I am trying to retrieve information has the following
>fields:
>
>TABLE-1:
>========
>
>       key     integer,
>       data_1  char(2),
>       data_2  smallint
>
>The data in this table has got the following physical sequence on the disk:
>
>                           FIGURE-1
>                           --------
>
>                key            data_1          data_2
>                =====================================
>
>                1000             ab              1
>                1000             ab              8
>                1000             ab              4
>                1000             ef              1
>                1000             ef              8
>                1000             ef              4
>                1000             cd              1
>                1000             cd              8
>                1000             cd              4
>
>
>I need to retrieve the data in the following sequence:
>
>
>                           FIGURE-2
>                           --------
>
>                key            data_1          data_2
>                =====================================
>
>                1000             ab              1
>                1000             ef              1
>                1000             cd              1
>                1000             ab              8
>                1000             ef              8
>                1000             cd              8
>                1000             ab              4
>                1000             ef              4
>                1000             cd              4
>
>
>The query
>
>       select * from table where key = 1000 order by data_2  OR
>
>the query
>
>       select * from table where key = 1000 group by key,data_2,data_1
>
>yields
>
>
>                           FIGURE-3
>                           --------
>
>                key            data_1          data_2
>                =====================================
>
>                1000             ab              1
>                1000             ef              1
>                1000             cd              1
>                1000             ab              4
>                1000             ef              4
>                1000             cd              4
>                1000             ab              8
>                1000             ef              8
>                1000             cd              8
>
>which disrupts the sequence in which the information is desired.
>
>There is another table, TABLE-2 which has the following columns:
>
>TABLE-2:
>========
>
>data_2         smallint
>data_2_desc    char(10)
>
>with the data in the following sequence:
>
>
>                           FIGURE-4
>                           --------
>
>                       data_2     data_2_desc
>                       ======================
>
>                       1               "desc_1"
>                       8               "desc_2"
>                       4               "desc_3"
>
>As you can see, the rows in this table are in the sequence in which the
>information is desired.
>
>Any solutions suggested would be appreciated with or without using TABLE-2.
>
>New fields cannot be added to the tables.
>
>
>Thanks much for your time,
>
>E.G.Nadhan
>egn@i88.isc.com
>OR
>{amdahl|att|cbosgd|spl1|sun|uwmcsd1|yclept|nucsrl} !laidbak!egn


You can join table 1 with table 2 to get the ordering of column data_2
correct. However, that will not ensure that column data_1 will be in the
correct order. You should also create a table 3 with the following data:

     DATA_1 DATA_1_ORDER
     ------ ------------
     ab                1
     ef                2
     cd                3

Then your query would look like:

     SELECT table_1.key, table_1.data_1, table_1.data_2
     FROM   table_1, table_2, table_3
     WHERE  table_1.data_2 = table_2.data_2
       AND  table_1.data_1 = table_3.data_1
     ORDER BY data_2_desc, data_1_order


If you are using a system that provides a function to do if-then-logic
and pattern matching (like the DECODE function in ORACLE), you could 
probably write the query without the joins. In ORACLE you could do:

     SELECT key, data_1, data_2
     FROM   table_1
     ORDER BY DECODE(data_2, '1', 1, '8', 2, '4', 3),
              DECODE(data_1, 'ab', 1, 'ef', 2, 'cd', 3)




Michael Wang
mwang@oracle.com