locked
Order of rows in select statement RRS feed

  • Question

  • Hi,

    I have a doubt, I have a table which has following rows and column
    Alpha1    Numer1
    A    1
    B    2
    A    2
    C    1
    C    2
    B    1

    If i query  select * from tabl1 order by Numer1
    if Results are
    Alpha1    Numer1
    A    1
    C    1
    B    1
    B    2
    A    2
    C    2

    if i run the same query again, will the order of Alpha1 column remain same or it may differ like below(i haven't ordered alpha1 column i have ordered only Numer1 column)

    Alpha1    Numer1
    A    1
    B    1
    C    1
    A    2
    B    2
    C    2

    Please clarify?

    Exact senario, I have asp application, which has report and export to excel, the order of rows of the report and export to excel differs for the same query. The query has inner join also.

    • Moved by Tom Phillips Tuesday, July 31, 2012 8:38 PM TSQL question (From:SQL Server Database Engine)
    Tuesday, July 31, 2012 9:18 AM

Answers

  • It may differ for Alpha1. However, Number1 would be always in order.

    In SQL server, we cant ensure the order of data unless it has been provided by "order by" of columns.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Proposed as answer by Vamshi.rb Tuesday, July 31, 2012 10:43 AM
    • Marked as answer by Previn Kumar Wednesday, August 1, 2012 6:09 AM
    Tuesday, July 31, 2012 9:22 AM

All replies

  • It may differ for Alpha1. However, Number1 would be always in order.

    In SQL server, we cant ensure the order of data unless it has been provided by "order by" of columns.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Proposed as answer by Vamshi.rb Tuesday, July 31, 2012 10:43 AM
    • Marked as answer by Previn Kumar Wednesday, August 1, 2012 6:09 AM
    Tuesday, July 31, 2012 9:22 AM
  • Latheesh is right. that there is no guarrantee for SQl server to return back the value in a particular order unless that you specifically use the Order By clause.

    so to guarantee the sort order as what you want, you need to add the second or third column to Order by for example like:

    select * from tabl1 order by Numer1, Alpja1


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Tuesday, July 31, 2012 10:26 AM