locked
How to ORDER BY a column RRS feed

  • Question

  • Hi Gurus, 

    I have few table joins in my sql query. All these tables have a column called sort_key1, which holds unique value for each table. Like for table1 =1, table2=2 .. etc.  and sort_key2 which is an identity column for each table.

    Finally, I would like my result set to be sorted by these two columns, first by sort_key1 and then sort_key2.

    How should I modify my sql query to get this sorted result set.? Please help me with this.

    Thursday, October 24, 2013 1:51 PM

Answers

  • 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

    Thursday, October 24, 2013 5:06 PM

All replies

  • 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
  • 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
  • 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
  • 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
  • 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

    Thursday, October 24, 2013 5:06 PM