How to order by a column index?

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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]

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

  Tuesday, December 3, 2013 5:38 PM
• 

  

  0

  Sign in to vote

  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