Re: subselect, order by and left join

On Mon, 2004-11-08 at 05:54, Stephan Szabo wrote:
> > If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
> > other table, is the order maintained? Or is PostgreSQL free to return the rows
> > in any order, after the join?
>
> AFAIK, you have no guarantees as to the output order unless you have
> another order by.  The join may destroy the ordering, so even if you get
> the ordering you want right now, you shouldn't rely on it.

Try http://www.varlena.com/varlena/GeneralBits/35.html

If you look under "Alphanumeric Sorting" about halfway down the page,
you will find the path toward the magic you are looking for.

A particularly nasty working example:

SELECT sort_order, col, code, description, units,
       TO_CHAR(min_value, 'FM99999999999D90') AS min_value,
       TO_CHAR(max_value, 'FM99999999999D90') AS max_value,
       value AS dv_text, id_datatype_value
 FROM ( SELECT *, (CASE WHEN (SUBSTRING(dv.value FROM '^[0-9\.]{1,3}') IS NOT NULL)
                        THEN (SUBSTRING(dv.value FROM '^[0-9\.]{1,3}')::numeric)
                        ELSE NULL
                    END) AS sort_order
         FROM datasheet ds JOIN datasheet_column dc  USING (id_datasheet)
                           JOIN datatype dt          USING (id_datatype)
                      LEFT JOIN datatype_value dv    USING (id_datatype)
        WHERE id_datasheet = '7') END_SORT_FU
ORDER BY col, sort_order, value

\<.