« SQL Injection Attack using T-SQL and HEXADECIMAL
AutoIT - Connecting to SQL Server. »

MSSQL - ORDER BY with a specific words

In SQL,the only option for Order by is either using Ascending or Descending. Sometimes,these two option is not possible to order the item based on your needs.

For example,let say you have a data like below and want to order by Type - Open then Close then Suspended.

Problem          Type
---------      --------
Problem 1      Suspended
Problem 2      Suspended
Problem 3      Open
Problem 4      Open
Problem 5      Close
Problem 6      Close

You cannot get the expected result based on normal Ascending or Descending. Then, how you can solve this problem using a normal SQL query ? Well, the solution is pretty much easy actually.

You just need to modify a litte bit your order by statement to get the expected. As for this case, you can solve using below query.

orderBy

select * from #XX order by replace(Type,’Open’,'A’)

So the trick is to replace your value that you want to make it appear at the top. In this case,’Open’ should be at the top,so we replace it with ‘A’ . Because A should be at the first position for asc order.

Hope it solve some of your SQL problems.

Till then…happy SQL’ING. :)

[Ask] [Bloglines] [del.icio.us] [Digg] [Furl] [StumbleUpon] [Technorati] More »

If you're new here, you may want to subscribe to my RSS feed or get my latest post directly in your mailbox. Thanks for visiting !

Related Post:
  • Happy Anniversary to me and my dear wife.
  • Generating a self signed SSL certificate in Ubuntu Hardy
  • Select count(distinct fieldA) in access
  • Interested in purchasing text link advertisement ??
  • Hello World



    • Can't find what you are looking for? Go Gooogle...
    Google

    11 Responses to “MSSQL - ORDER BY with a specific words”

    1. Fadzuli on July 7th, 2008 at 6:57 pm Using Mozilla Firefox Mozilla Firefox 3.0 on Windows Windows XP

      Does this work with MYSQL also?

      http://www.fadzuli.com

      Fadzuli’s last blog post..Follow ups

    2. arejae on July 7th, 2008 at 7:51 pm Using Internet Explorer Internet Explorer 7.0 on Windows Windows XP

      yes,this works also in MYSQL. :)

    3. drizzt on July 9th, 2008 at 8:19 pm Using Mozilla Firefox Mozilla Firefox 2.0.0.14 on Windows Windows XP

      abe … wubi tu reliable ke? takut install2 kea jane OS kawe … haha

      drizzt’s last blog post..Come & Lick

    4. thinker on July 10th, 2008 at 11:45 pm Using Opera Opera 9.51 on Windows Windows XP

      bgs ar blog demo nie. respek. sek kelate hebat!

    5. arejae on July 11th, 2008 at 9:42 pm Using Mozilla Firefox Mozilla Firefox 3.0 on Linux Linux

      @drizz: kawe sendiri tok rajin try lagi wubi tuh abey weh. tapi secara teori,guna wubi nie utk elakkan dari menjahanamkan OS kito.eheh…acu try teh abey,bereh kawe raso.

      @thinker:blog demo pun bijok gok.teruja jugok kawe tengok.ehehe… :)

    6. Steven on July 24th, 2008 at 7:54 am Using Mozilla Firefox Mozilla Firefox 2.0.0.12 on Windows Windows XP

      What if you want multiple replace functions in a simple SQL statement? I tried it in many different ways and each time it didn’t work? Do you have a suggestion?

    7. arejae on July 27th, 2008 at 1:06 pm Using Mozilla Firefox Mozilla Firefox 3.0.1 on Linux Linux

      Hi Steven, I’m not very sure what you mean by multiple replaces.Is it something like below.

      select * from #XX order by
      replace(replace(Type,’Open’,’A’),’Suspended’,'B’)

      this will order by - Open then Suspended then Close.

    8. rauhunt on August 1st, 2008 at 7:58 am Using Mozilla Firefox Mozilla Firefox 3.0.1 on Windows Windows Vista

      Arejae,

      Sorry coz my comment is nothing to do with your entry. Just to say you deserve my recognition for linking-up my blog (silly me ..just realised today). I’ll put your link on mine at some point, hopefully by next week. Have been quite busy at the moment as I need to move to other lab which took me more or less 8 hours yesterday.

      Another thing, from Pak Zawi’s newest blog I noticed that you were in SIS 89-93. Then you must know Fakrul Anwar Razali dont you?

      rauhunt’s last blog post..Barcelona menyerang St Andrews

    9. arejae on August 1st, 2008 at 8:22 am Using Mozilla Firefox Mozilla Firefox 3.0.1 on Linux Linux

      Rauhunt, bereh..takdo hal. wah..8 jam dalam lab jer…study tok hengat tuh.ehehe…bereh..blajar molek2.

      oo…kenal lar dengan fakrul tuh..demo nie adik dio ko ? ehee

    10. rauhunt on August 1st, 2008 at 2:39 pm Using Mozilla Firefox Mozilla Firefox 3.0.1 on Windows Windows Vista

      bukann 8 jam dlm lab ..tp perjalane 8 jam dari scotland ke southeast england ..tukar lab. fakrul tu spupu kawe

      rauhunt’s last blog post..Barcelona menyerang St Andrews

    11. arejae on August 2nd, 2008 at 12:11 am Using Mozilla Firefox Mozilla Firefox 3.0.1 on Linux Linux

      ooo….jauh kaklos tuh.

      kawe lamo benar dok jupo fakrul tuh…

    Leave a Reply

    AWSOM Powered