2

I had a requirement like select * from USER where firstname IN ('nexus', 'samsung', 'apple');

On executing this query , i get results based on insertion order of rows in database.But i need the results exactly same way, i given for IN parameter. for ex : the above query should give results like

nexus samsung apple

and not any other order like. samsung nexus apple

What can i use with the above query to get selection results in the order i given?

srinivas
  • 474
  • 7
  • 14
  • Similar question (restricted to Java): http://stackoverflow.com/questions/10813767/how-to-tell-oracle-to-sort-by-a-specific-sort-order-passed-in-from-java – Thilo Dec 11 '14 at 03:35
  • Similar question (for MySQL, not helpful for Oracle): http://stackoverflow.com/questions/2813884/how-do-you-keep-the-order-using-select-where-in – Thilo Dec 11 '14 at 03:37

1 Answers1

2

One method is to use case:

order by (case when firstname = 'nexus' then 1
               when firstname = 'samsumg' then 2
               when firstname = 'apple' then 3
          end)

Another method that has less typing:

order by instr(',nexus,samsung,apple,', ',' || name || ',')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Is there any other way, as i have around 100 to 200 firstnames for a select statement.Its more difficult to add then 1,2 for each firstnames. – srinivas Dec 11 '14 at 03:36
  • I don't think there is a good way for Oracle. How much data do you have? If in the low thousands, probably better to sort client-side in application code. – Thilo Dec 11 '14 at 03:41
  • 1
    @srinivas . . . Where do the names come from? If they are already in another table or the result of a query, then that would simplify this query. – Gordon Linoff Dec 11 '14 at 03:45
  • I will get the names from excel sheet – srinivas Dec 11 '14 at 03:57
  • I'd thought, Gordon's `order by instr(',nexus,samsung,apple,', ',' || name || ',')` to save you from the typing. ?? Alternatively, you could number the items (easy in Excel), and then use a regex to assemble the `case`… Or turn the numbered items into an auxiliary table in the database to be used for a `JOIN` based sort criterion. Either of these will require rework if you have to add items not just to the end of the ordered list… – Abecee Dec 11 '14 at 04:39
  • "i have around 100 to 200 firstnames for a select statement" - how are you passing those names to the IN clause? – APC Dec 11 '14 at 05:32
  • @APC, i will get from excel and format it in notepad to add quotes and pass to that IN clause. – srinivas Jan 29 '15 at 12:42