Oracle SQL - fetching a record at last

SELECT * FROM
(
SELECT city from addr_tab
UNION ALL
SELECT 'zzzz' AS city from dual
)ORDER BY city

I am using the above approach to retrieve the 'zzzz' as last record of the table. But I don't think this would work all the time, as the city theoretically might be something bigger than "zzzz" alphabetically. Is there any other robust approach to get this harcoded record开发者_运维知识库 as the last record?. I need this for reporting using oracle reports. Any help would be most welcome.

Add another virtual column (order_col) to use for ordering. The query would end up something as follows.

SELECT * FROM
(
SELECT city, 1 as order_col from addr_tab
UNION ALL
SELECT 'zzzz', 2 as order_col AS city from dual
)ORDER BY order_col, city

THis way you can guarantee that 'zzzz' will always be last since the primary ordering column (order_col) is guaranteed to have a lower value for 'zzzz' than for all the rest of the records.

Well, as others have said (in comment and answer) there is probably a better way to approach the problem from the beginning; but if you need a temporary hack read on!...

Call the last record whatever you want...
'zzzz' is fine...
and then use a custom ORDER BY like:

SELECT * 
  FROM (
       SELECT city 
         FROM addr_tab
       UNION ALL
       SELECT 'zzzz' AS city 
         FROM dual
       )
ORDER BY 
    CASE WHEN city = 'zzzz' THEN 1 ELSE 0 END,
    city;

this can be solve by following query also

 SELECT * FROM 
 ( SELECT city from addr_tab order by city)
   UNION ALL 
  SELECT 'zzzz' from dual;

now got one more style to do this

with 
b1 as 
(
SELECT city from addr_tab order by city
)
select city from b1
union all 
SELECT 'zzzz' from dual;

so any one you can use ....