2 Replies Latest reply on May 2, 2012 9:37 AM by 920172

    Using ORDER BY for column that's not in SELECT statement of select list?

      Hi all.

      I have a select list on an APEX page at apex.oracle.com, and I would like to order this by the value of column UnitOrder.
      The SQL is as follows:
      SELECT DISTINCT foodunit.name AS display_value, foodunit.foodunitid AS return_value
      FROM foodunit, food
      WHERE foodunit.foodid = :P18_FOODID
      ORDER BY foodunit.unitorder ASC;
      Off course, this code won't work. And since it's a select list, I can't just add foodunit.unitorder to the SELECT statement. Also, I can't get rid of the DISTINCT, because then for some reason it tends to display an infinite amount of the same row...
      The point of ordering it by UnitOrder is that all units who begin with gram, ml, mg, kg,... etc, will be shown on top of the select list. All other food units will be shown on the second place and so on.
      The column UnitOrder holds either 1 or 2 as a value. 1 is the value for all rows that should be displayed at the top of the select list, 2 is the value for all the rest.

      So, what I want is that all rows in which the UnitOrder is 1, get displayed on top of the select list. Usually only 2 list items get shown in the select list at a time, depening on which food item a user has selected. The select list then refines to certain rows that match the foodid etc etc..

      If anyone has an idea, I'd be very happy to hear about it.