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

\<.