locked
How to order by a column index? RRS feed

  • Question

  • Hi there

    The Pervasive 11 allows sorting with no reference to a column name, like:

    select RecID, RecId, Description from SelfSchedGroups  order by 1

    The clause above will order by the first column.

    How accomplish that for the MS SQL 2012?

    Simply put, I'm struggling with the column ambiguity for the clause, came from Pervasive:

     select RecID, RecId, Description from SelfSchedGroups  order by RecId

    It is absolutely Ok for Pervasive, but throws the "ambiguity" exception for the MS SQL.

    How to fix it with making no change to a "select...from" list of field names?

    Thanks.

    Tuesday, December 3, 2013 4:56 PM

Answers

  • Also in SQL Server you can use the column ordinate to order the result:

     
    SELECT name, create_date, modify_date
    FROM sys.objects
    ORDER BY 3 DESC

    But you should avoid it, it's bad programming style.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Renziglov Tuesday, December 3, 2013 5:40 PM
    Tuesday, December 3, 2013 5:02 PM

All replies

  • Also in SQL Server you can use the column ordinate to order the result:

     
    SELECT name, create_date, modify_date
    FROM sys.objects
    ORDER BY 3 DESC

    But you should avoid it, it's bad programming style.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Renziglov Tuesday, December 3, 2013 5:40 PM
    Tuesday, December 3, 2013 5:02 PM
  • As Olaf mention,

    I highly recommend not to use this form of writing. Not only is it bad writing, Moreover, using SQL server it can cause very strange behavior sometimes!

    declare @A int
    
    SET @A = 0
    select  @A = @A + A from (values (1),(2),(3),(4)) T1(A)
    --order by 1
    select @A
    
    SET @A = 0
    select  @A = @A + A from (values (1),(2),(3),(4)) T1(A)
    order by 1
    select @A
    

    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Tuesday, December 3, 2013 5:49 PM
    Tuesday, December 3, 2013 5:38 PM
  • Hi there

    The Pervasive 11 allows sorting with no reference to a column name, like:

    select RecID, RecId, Description from SelfSchedGroups  order by 1

    The clause above will order by the first column.

    How accomplish that for the MS SQL 2012?

    Simply put, I'm struggling with the column ambiguity for the clause, came from Pervasive:

     select RecID, RecId, Description from SelfSchedGroups  order by RecId

    It is absolutely Ok for Pervasive, but throws the "ambiguity" exception for the MS SQL.

    How to fix it with making no change to a "select...from" list of field names?

    Thanks.

    SQL server also supports specifying ordinal position of the column in ORDER BY clause. But its not advisable as if addition of a new column changes the position the output may not be as expected.

    Also, if same column name ( or alias name) is present twice in select the order by of that name is not acceptable.  One has to distinguish the column names if the order by to be applied on them.

    RecID and Recid means different in Pervasive ? Does that mean the language is case sensitive?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, December 3, 2013 5:49 PM
    Answerer