Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sun-barr!apple!motcsd!mcdcup!mcdchg!tellab5!laidbak!egn
From: egn@i88.isc.com (E. G. Nadhan)
Newsgroups: comp.databases
Subject: SQL Problem
Keywords: INFORMIX SQL Query 4GL
Message-ID: <1991Feb12.023644.14504@i88.isc.com>
Date: 12 Feb 91 02:36:44 GMT
Sender: usenet@i88.isc.com (Usenet News)
Organization: INTERACTIVE Systems Corporation, Naperville, IL
Lines: 115

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