locked
Without using orderby clause RRS feed

  • Question

  • Hi,

    without using orderby clause how can get the data in the format i need?

    for example

    select         id,Name,NBR_number,ACTN_CODE   from table1... in this i need to have order by id with out using orderby clause..how can i achieve this?

    thanks,

    aravind


    • Edited by aravind T Thursday, April 19, 2012 8:51 PM
    Thursday, April 19, 2012 8:49 PM

Answers

  • There is no guaranteed way to do that without the ORDER BY clause. You can create a clustered index on ID, but that will not guarantee the order every time you retrieve the data because the data in the index could become out of order with inserts, page splits, etc..

    So your best bet is ORDER BY, or may be on the front end if there is a way to do it since I'm not a programmer. 


    Abdallah El-Chal, PMP

    • Proposed as answer by Naomi N Thursday, April 19, 2012 9:00 PM
    • Marked as answer by KJian_ Thursday, April 26, 2012 6:31 AM
    Thursday, April 19, 2012 8:53 PM
  • without using order by clause how can get the data in the format i need?


    You can write your own sort. That is pretty much the end of list. SSRS has its sort facility for reports.

    Complex ORDER BY:

    http://www.sqlusa.com/bestpractices/order-by/


    Kalman Toth SQL SERVER & BI TRAINING

    • Marked as answer by KJian_ Thursday, April 26, 2012 6:31 AM
    Friday, April 20, 2012 12:35 AM
  • need to have order by id with out using orderby clause..how can i achieve this?
    As mentioned  by Abdallah and Kalman, its not possible. Order by is needed if you want "guarantee"

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked as answer by KJian_ Thursday, April 26, 2012 6:31 AM
    Friday, April 20, 2012 12:55 AM

All replies

  • There is no guaranteed way to do that without the ORDER BY clause. You can create a clustered index on ID, but that will not guarantee the order every time you retrieve the data because the data in the index could become out of order with inserts, page splits, etc..

    So your best bet is ORDER BY, or may be on the front end if there is a way to do it since I'm not a programmer. 


    Abdallah El-Chal, PMP

    • Proposed as answer by Naomi N Thursday, April 19, 2012 9:00 PM
    • Marked as answer by KJian_ Thursday, April 26, 2012 6:31 AM
    Thursday, April 19, 2012 8:53 PM
  • without using order by clause how can get the data in the format i need?


    You can write your own sort. That is pretty much the end of list. SSRS has its sort facility for reports.

    Complex ORDER BY:

    http://www.sqlusa.com/bestpractices/order-by/


    Kalman Toth SQL SERVER & BI TRAINING

    • Marked as answer by KJian_ Thursday, April 26, 2012 6:31 AM
    Friday, April 20, 2012 12:35 AM
  • need to have order by id with out using orderby clause..how can i achieve this?
    As mentioned  by Abdallah and Kalman, its not possible. Order by is needed if you want "guarantee"

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked as answer by KJian_ Thursday, April 26, 2012 6:31 AM
    Friday, April 20, 2012 12:55 AM