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 \<. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
