egn@i88.isc.com (E. G. Nadhan) (02/12/91)
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!egnbengsig@dk.oracle.com (Bjorn Engsig) (02/13/91)
Article <1991Feb12.023644.14504@i88.isc.com> by egn@i88.isc.com (E. G. Nadhan) says: | |The data in this table has got the following physical sequence on the disk: In a relational database system, you should not care about the physical structure of the storage, and in particular not the sequence of rows. You might even see the sequence change if you update one of the rows. Anyway, you write: |I need to retrieve the data in the following sequence: | | [ a non-sequentail ordering 1,8,4 is wanted ] | If you know the set of possible values for data_2, you can use select * from <your table> order by decode(data_2, 1,1, 8,2, 4,3, 0); -- Bjorn Engsig, ORACLE Corporation, E-mail: bengsig@oracle.com, bengsig@oracle.nl "Stepping in others footsteps, doesn't bring you ahead"
mwang@oracle.com (Michael Wang) (02/13/91)
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
lugnut@sequent.UUCP (Don Bolton) (02/14/91)
In article <1253@dkunix9.dk.oracle.com> bengsig@dk.oracle.com (Bjorn Engsig) writes: >Article <1991Feb12.023644.14504@i88.isc.com> by egn@i88.isc.com (E. G. Nadhan) says: >| >|The data in this table has got the following physical sequence on the disk: >In a relational database system, you should not care about the physical >structure of the storage, and in particular not the sequence of rows. You >might even see the sequence change if you update one of the rows. > >Anyway, you write: >|I need to retrieve the data in the following sequence: >| >| [ a non-sequentail ordering 1,8,4 is wanted ] >| >If you know the set of possible values for data_2, you can use >select * from <your table> >order by > decode(data_2, 1,1, 8,2, 4,3, 0); I'm not aware Informix has the decode function available. Better check my book. Ie: RTFM :-) >-- >Bjorn Engsig, ORACLE Corporation, E-mail: bengsig@oracle.com, bengsig@oracle.nl > > "Stepping in others footsteps, doesn't bring you ahead"