SQL: Is there any way to sort all data by all columns using order by clause for exampe: order by *

Question

I want to write like this code:

select col1,col2,col3,col4
from x
where ----
order by all

More details: I get my columns dynamically from database using Sp_executesql to execute my SQL statement and I don't know what the number of returned columns is, and I want to sort these columns by all these dynamically columns.

This is my code:

ALTER PROCEDURE [dbo].[SP_Get_Trx_Log_details] 
  -- Add the parameters for the stored procedure here   
  @thread_id INT, 
  @frd_id_BU VARCHAR(max), 
  @and_cond  VARCHAR(max)

AS 
  BEGIN 
      DECLARE @tbl_name NVARCHAR(300) 
      DECLARE @type NVARCHAR(10) 
      SELECT @type = Upper(thread_type) 
      FROM   dbo.tbl_frd_threads 
      WHERE  thread_id = @thread_id 

      IF( ( @type = 'M' 
             OR @type = 'Q' 
             OR @thread_id = 50 ) 
          AND @thread_id <> 49 ) 
        BEGIN 

            SET @tbl_name = 'dbo.tbl_frd_details_member_qa det LEFT JOIN dbo.tbl_frd_actions act  ON det.frd_det_id = act.frd_id AND det.thread_id = act.thread_id AND act.action_type = ''C''  '
        END 
      ELSE IF ( ( @type = 'P' 
              OR @thread_id = 49 ) 
           AND @thread_id <> 50 ) 
        BEGIN 

            SET @tbl_name = 'dbo.tbl_frd_details_provider det LEFT JOIN dbo.tbl_frd_actions act  ON det.frd_det_id = act.frd_id AND det.thread_id = act.thread_id AND act.action_type = ''C''  '
        END 

      IF ( @thread_id = 1000 ) -- JOIN details tbl with action tbl 
        BEGIN 
            SET @tbl_name = 'dbo.tbl_frd_details_member_qa det LEFT JOIN dbo.tbl_frd_actions act  ON det.frd_det_id = act.frd_id AND det.thread_id = act.thread_id AND det.action_type = act.actions  AND act.action_type = ''C''    '--  AND convert(datetime,act.action_date)  = '''+Convert(varchar(50),@action_date)+''''
        END 

    DECLARE @SelectStmt AS NVARCHAR(max) 
    DECLARE @SelectColumns AS NVARCHAR(max) 

    SELECT @SelectColumns = trx_log_cols 
    FROM   tbl_frd_threads 
    WHERE  thread_id = @thread_id 
           AND Isnull(trx_log_cols, '-1') != '-1' 
           AND trx_log_cols != '' 

    SET @SelectStmt = 'SELECT * FROM ( SELECT  Distinct' + isnull(@SelectColumns ,'-1')+ ' FROM ' 
                      + @tbl_name + ' WHERE   det.frd_id =''' 
                      + @frd_id_BU + '''    ' + @and_cond +') A'
                      + ' ORDER BY A.[Action Date] , 2,3,5,7'
    -- Here I don't know the number of returned columns BUT I need to sort the returned columns by all columns or by odded number 

    EXEC Sp_executesql  @SelectStmt 
END

Clear ?

Thanks

How exactly do you want your data sorted? Please give an example. — Thorsten Kettner, Apr 25 '16 at 13:19
i want to use order by to sort by all column as in select stmt — Mohamed Nasr, Apr 25 '16 at 13:21
i got my columns dynamically i use Sp_executesql to execute my sql stmt and i don't know what is the number of returned columns and i want to sort these columns by all these dynamically columns — Mohamed Nasr, Apr 25 '16 at 13:23
I mean give an **example**. Show some example rows and columns and how you want them sorted. — Thorsten Kettner, Apr 25 '16 at 13:23
Then you can add your columns dynamically in your `order by` too, the same way you are getting them in your `select` — Haytem BrB, Apr 25 '16 at 13:25
@haytem how? i don't know the number of returned column...so i can select but i can not use order by ! — Mohamed Nasr, Apr 25 '16 at 13:28
@SirajulHaq what i want to order by dynamically returned columns which i don't know it's number — Mohamed Nasr, Apr 25 '16 at 13:29
Show us how do you get these columns in your `select` statement — Haytem BrB, Apr 25 '16 at 13:29
Even after your edit, it is still unclear what you are asking. Sort columns by columns? Can't you give an **example**? — Thorsten Kettner, Apr 25 '16 at 13:30
I added my code and i hope you can understand my question now..@ThorstenKettner — Mohamed Nasr, Apr 25 '16 at 13:34
Well, I for my part don't. What do you want to sort? Columns? Or rows? Are you or are you not looking for `select col1, col2 from ... order by col1, col2` and `select col1, col2, col3, col4 from ... order by col1, col2, col3, col4`? — Thorsten Kettner, Apr 25 '16 at 13:43
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Apr 25 '16 at 13:53
@ThorstenKettner i want to sort all returned data by these columns — Mohamed Nasr, Apr 25 '16 at 14:09
No. You must either name the columns (in your case extract the resulting column names, i.e. the alias where given, else the column name), or use numbers, i.e. 1 for the first column in your select list, 2 for the second and so on, which should be easier than parsing names and aliases. — Thorsten Kettner, Apr 25 '16 at 15:27
The easiest way is `order by 1, 2,3` (1-based number of the column in the column list) — Alex Kudryashev, Apr 25 '16 at 15:43
@AlexKudryashev Thank you for your help.. please read comments under answer to understand what i want exactly.. — Mohamed Nasr, Apr 26 '16 at 09:55

Thorsten Kettner · Accepted Answer · 2016-04-25T14:28:28.373

1

I still don't know, if I understand what you are asking or not. If you want to order your rows in the order of your columns, i.e. first by first column, then by second column, and so on, then haytem has given you the answer an hour ago in the comments section: Build the ORDER BY clause exactly the same way as you build your SELECT clause.

SET @SelectStmt = 'SELECT * FROM ( SELECT  Distinct' + isnull(@SelectColumns ,'-1')
                  + ' FROM ' + @tbl_name 
                  + ' WHERE   det.frd_id =''' + @frd_id_BU + '''    ' + @and_cond +') A'
                  + ' ORDER BY ' + isnull(@SelectColumns ,'1')

In case there are no columns in @SelectColumns, you select -1 and order by 1, i.e. the first and only column.

edited Apr 25 '16 at 14:28

answered Apr 25 '16 at 14:25

Thorsten Kettner

89,309
7
49
73

But the @SelectColumns contain aliases!! – Mohamed Nasr Apr 25 '16 at 14:27
Ah, okay, there are aliases. That was not to be expected, as the columns are retrieved from a table. Can you change the table so it contains column names and alias names separated? That would be the best option I guess. An alternative would be to generate a list of 1,2,3,4, ... according to the number of commas in @SelectColumns plus 1. I don't know if this is easily achievable in your DBMS, however. – Thorsten Kettner Apr 25 '16 at 14:35
your answer shows me you understand my question well..but as i said the @selectColumns contain aliases!! how can i solve this issue?? – Mohamed Nasr Apr 25 '16 at 14:37

SQL: Is there any way to sort all data by all columns using order by clause for exampe: order by *

1 Answers1