Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: need help on custom order by (dunno if this is the correct sub forum)

    Greetings!

    im using oracle 9 and im using
    JDBC connection for my application.

    Basically this is what happens, i have this table called symbol, and in this i have a column called name.

    My application right now do a search in the name.

    For example, when user enters "micro" in the search box, the application will run a sql statement which will look into symbol table with name that contains "micro" in it. Then right now it is ordered alphabetically.

    What i want to do is to order it in this way:
    - names that start with the word the user search will be the first ones to appear
    - and the rest will be sorted alphabetically

    for example if user search for microsoft it will return something like

    microsoft
    micro virus
    microworld
    abe micro goods
    trend micro AV

    something like that.
    Stumbled across order by case.. tried that, it doesnt work. Anyone can help me with this?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER
        BY CASE WHEN name LIKE 'micro%'
                THEN 0 ELSE 1 END
         , name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    2

    Thanks

    Thanks!

    It worked beautifully, i just need to add upper(name) and it works with any upper/lower case combination

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •