how to order the column by a field in oracle?

0
votes

If I have a table something like this example:

 - Table: Users
 - id - name -  city
 - 1    George  Seattle
 - 2    Sam     Seatle-2
 - 3    John    New York
 - 4    Amy     New York-2
 - 5    Eric    Chicago
 - 6    Nick    New York-3

And using that table I want to my query to return the rows which contain New York first,New York-2,---- and then the rest of the rows alphabetized by city. Is this possible to do using only one query?

2
sqloracle
You can do this by introducing another column of type boolean called ord and setting true to new york cities. Then you do select * from table order by ord, city – Andrew
@Andrew Oracle SQL does not have a boolean type. You would use a character type or an number type. – Brian Leach

2 Answers

2
votes

The order by clause supports multiple keys, including expressions:

order by (case when city like 'New York%' then 1 else 2 end),
         city
1
votes
SELECT   id,
    name,
    city
  ORDER BY (
    CASE
      WHEN city LIKE 'New York%'
      THEN 1
      ELSE 2
    END),
    city ASC;