How to ORDER BY a column

All replies

• 

  

  0

  Sign in to vote

  You said you have these column in every table.that means resulting output will have these columns in the same row.that means table1.sort_key1,table1.sort_key2,table2.sort_key1,table2.sort_key2

  What is your order criteria in that case.Do you want something like this.

  select A.Column1,B.Column1,A.Sort_Key1,A.Sort_Key2,B.Sort_Key1,B.Sort_Key2
  From Table1 A Join Table2 B on A.ID=B.ID
  Order by A.Sort_Key1,B.Sort_Key1,A.Sort_Key2,B.Sort_Key2

  Post some sample DDL and sample data input and output.

  ---

  Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

  
  

  • Edited by Vinay Valeti Thursday, October 24, 2013 2:09 PM
  • Proposed as answer by Saeid Hasani Thursday, October 24, 2013 2:30 PM

  Thursday, October 24, 2013 2:09 PM
• 

  

  0

  Sign in to vote

  You said you have these column in every table.that means resulting output will have these columns in the same row.that means table1.sort_key1,table1.sort_key2,table2.sort_key1,table2.sort_key2

  What is your order criteria.Do you want something like this.

  select A.Column1,B.Column1,A.Sort_Key1,A.Sort_Key2,B.Sort_Key1,B.Sort_Key2
  From Table1 A Join Table2 B on A.ID=B.ID
  Order by A.Sort_Key1,B.Sort_Key1,A.Sort_Key2,B.Sort_Key2

  Post some sample DDL and sample data input and output.

  ---

  Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

  Hi Vinay Valeti, 

   Yes, you are right "these columns in the same row". We will've a SAS program reading this sql query o/p and should put them in the order according to predefined order in sort_key1 field. The very point I was missing all this time. But I want to do this ordering in SQL instead of SAS. I have almost 13 joins and when I tried to tweak my query like above it is taking lot of time. 

  My sample data.

  table1:

  sort_key1  sort_key2     col1    col2

  1                1               aaa     fddf1                2               ddd     fdfd

  table2: 

  sort_key1  sort_key2     col1    col2

  2                1               bbb    ddd
  2                2               asas    asdfd

  My O/P.

  sort_key1  sort_key2     col1    col2

  1                1               aaa     fddf
  1                2               ddd     fdfd

  2                1               aaa     fddf
  2                2               ddd     fdfd

  I'm trying to get the O/P like this. Please help me with this. ? 

  Thursday, October 24, 2013 2:50 PM
• 

  

  0

  Sign in to vote

  In the above output.It looks like you are doing a union.not a join 

  If that is what you want you can try this..

  select Col1,Col2,sort_key1,sort_key2 From Table1
  union all
  select Col1,Col2,sort_key1,sort_key2 From Table2
  Order by sort_key1,sort_key2

  
  

  ---

  Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

  Thursday, October 24, 2013 3:04 PM
• 

  

  0

  Sign in to vote

  Actually, I want to join the data but I forgot to represent that above. I only want to O/p the fields that are in both those tables. 

  I'm doing INNER JOIN in my query.

  Thursday, October 24, 2013 3:48 PM
• 

  

  0

  Sign in to vote

  How do you join in the above scenario.you have given 2 rows in the Table1 and 2 rows in Table2.How did you get four rows in the output.is it a cross join

  Try this Order by.

  Order by isnull(T1.sort_key1,T2.Sort_Key1),isnull(T1.sort_key2,T2.sort_key2)

  If will be easy to answer if you post data that resembles your input and output and join conditions.
  

  
  

  ---

  Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

  • Proposed as answer by Allen Li - MSFT Sunday, November 3, 2013 2:13 PM
  • Marked as answer by Allen Li - MSFT Monday, November 11, 2013 6:34 AM

  Thursday, October 24, 2013 5:06 PM