Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!utgpu!water!watmath!clyde!rutgers!sri-spam!mordor!lll-tis!ptsfa!rtech!eric
From: eric@rtech.UUCP
Newsgroups: comp.databases
Subject: Re: (Re)sorting blanks in SQL
Message-ID: <931@rtech.UUCP>
Date: Thu, 18-Jun-87 23:31:12 EDT
Article-I.D.: rtech.931
Posted: Thu Jun 18 23:31:12 1987
Date-Received: Sat, 20-Jun-87 06:49:19 EDT
References: <1977@trwrb.UUCP>
Distribution: na
Organization: Relational Technology, Alameda CA
Lines: 55

in article <1977@trwrb.UUCP>, ries@trwrb.UUCP (Marc Ries) says:
> 
> 
>   [While this is being used with Informix's SQL-ACE report writer,
>   the problem may be somewhat generic]
> 
>   I have a group  of  users  who  are  used  to  seeing  (FORTRAN
>   created)  reports  with  all of the (sorted) items with "blank"
>   attributes at the end of the report.
> 
>   ACE (I assume SQL, too?)  offers  two  options:  ASCending  and
>   DECending,  with ASC the default and the "blanks" at the top of
>   the report.
> 
>   Descending is no good because while I have the  blanks  at  the
>   end of the report, the user sees "z-A" in the report main.
> 

	According to the latest ANSI SQL spec, comparisons and sort
sequence is determined by the DBMS implementor-defined collating sequence.
In most cases, this means that the sequence that ASCII or EBCDIC provides
is used, which means that blanks come before numbers and letters. However,
this doesn't help you solve your problem; it only demonstrates that, in
this respect, Informix is DB2, ANSI, and XOPEN SQL compatible (DB2, ANSI,
and XOPEN are the "big 3" of the SQL standard busness).

	Suppose that the name of your table is 'foo' and that the name of
column that you want to sort by is 'bar'. The query, in SQL, that would 
produce your current results with the blanks at the top would be:

	select * from foo where <your_qualification_here> group by bar

The query:

	select 1 as ordatt, foo.* from foo
			where bar != " " and <your_qualification_here>
		union all
	select 2 as ordatt, foo.* from foo
			where bar = " " and <your_qualification_here>
		group by ordatt, bar

should produce your desired results with blanks at the bottom (it may not
be pretty, but it should work). Actually,
this is very similiar to the solution that was presented in the original
request, except that this makes the DBMS do the work and, therefore, is
easier to implement. Note that the above is written using ANSI SQL; I don't
know whether Informix can handle it. Note also that duplicates will be removed 
if you use 'union' instead of 'union all'.

		eric

-- 

			Eric Lundblad
		   ucbvax!mtxinu!rtech!eric