SQL Server: Order by Parameters in In Statement

order by a parameter

You have 2 options, either use a CASE statement, or use dynamic sql

This would be an example of the CASE statement

DECLARE @Table TABLE(
        Col1 VARCHAR(10),
        Col2 VARCHAR(10)
)

DECLARE @OrderBy VARCHAR(100)

SET @OrderBy = 'Col1'

SELECT  *
FROM    @Table
ORDER BY 
        CASE
            WHEN @OrderBy = 'Col1' THEN Col1
            WHEN @OrderBy = 'Col2' THEN Col2
            ELSE Col1
        END

And this would be and example of dynamic sql

CREATE TABLE #Table (
        Col1 VARCHAR(10),
        Col2 VARCHAR(10)
)

DECLARE @OrderBy VARCHAR(100)

SET @OrderBy = 'Col1'

DECLARE @SqlString NVARCHAR(MAX)

SELECT @SqlString = 'SELECT * FROM #Table ORDER BY ' + @OrderBy

EXEC(@Sqlstring)

DROP TABLE #Table

Order by ASC or DESC based on parameter?

You have the right idea but just need to change the way your CASE is working a little:

ORDER BY
  CASE WHEN @IsDescOrder = 1 THEN DateKey ELSE '' END DESC,
  CASE WHEN @IsDescOrder = 0 THEN DateKey ELSE '' END ASC

This contains two clauses to order by, an descending order clause followed by an ascending one.

When @IsDescOrder is 1 then the descending clause is used, otherwise the ascending. By supplying ELSE '', this ensures that the unwanted order (either ASC or DESC) is effectively ignored.

EDIT

As pointed out by OP, this caused an error if the order column was numeric. To solve this, replace the empty string ('') with 0:

ORDER BY
  CASE WHEN @IsDescOrder = 1 THEN DateKey ELSE 0 END DESC,
  CASE WHEN @IsDescOrder = 0 THEN DateKey ELSE 0 END ASC

This works in the same was as the solution with the empty string, but prevents the type conversion error when using numeric columns.

Order By using a parameter for the column name

You should be able to do something like this:

SELECT *
FROM
    TableName
WHERE
    (Forename LIKE '%' + @SearchValue + '%') OR
    (Surname LIKE '%' + @SearchValue + '%') OR
    (@SearchValue = 'ALL')
ORDER BY 
    CASE @OrderByColumn
    WHEN 1 THEN Forename
    WHEN 2 THEN Surname
    END;

• Assign 1 to @OrderByColumn to sort on Forename.
• Assign 2 to sort on Surname.
• Etc... you can expand this scheme to arbitrary number of columns.

---

Be careful about performance though. These kinds of constructs may interfere with query optimizer's ability to find an optimal execution plan. For example, even if Forename is covered by index, query may still require the full sort instead of just traversing the index in order.

If that is the case, and you can't live with the performance implications, it may be necessary to have a separate version of the query for each possible sort order, complicating things considerably client-side.

SQL stored procedure passing parameter into order by

Only by being slightly silly:

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto
ORDER by CASE WHEN @orderby='ASC' THEN Pareto END,
         CASE WHEN @orderby='DESC' THEN Pareto END DESC

You don't strictly need to put the second sort condition in a CASE expression at all(*), and if Pareto is numeric, you may decide to just do CASE WHEN @orderby='ASC' THEN 1 ELSE -1 END * Pareto

(*) The second sort condition only has an effect when the first sort condition considers two rows to be equal. This is either when both rows have the same Pareto value (so the reverse sort would also consider them equal), of because the first CASE expression is returning NULLs (so @orderby isn't 'ASC', so we want to perform the DESC sort.

---

You might also want to consider retrieving both result sets in one go, rather than doing two calls:

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS

SELECT * FROM (
    SELECT
       *,
       ROW_NUMBER() OVER (ORDER BY Pareto) as rn1,
       ROW_NUMBER() OVER (ORDER BY Pareto DESC) as rn2
    FROM (
        SELECT Peroid1.Pareto
        FROM dbo.Peroid1
        GROUP by Pareto
    ) t
) t2
WHERE rn1 between 1 and 10 or rn2 between 1 and 10
ORDER BY rn1

This will give you the top 10 and the bottom 10, in order from top to bottom. But if there are less than 20 results in total, you won't get duplicates, unlike your current plan.

Select records with order of IN clause

You have a couple of options. Simplest may be to put the IN parameters (they are parameters, right) in a separate table in the order you receive them, and ORDER BY that table.

How to make a query with a custom order by parameter using array?

If your version of SQL Server supports JSON querying (i.e. 2016+), you can use openjson() function to number the elements of your array, and then use that number for sorting:

declare @Arr nvarchar(max) = '[0, 1, 21, 2, 22, 23, 24, 25, 3, 27, 35, 36, 28, 37, 38, 4, 29, 5, 34, 6, 7, 8, 9, 10, 11, 12]';

SELECT q.qmaQuoteAssemblyID,
  q.qmaPartID,
  q.qmaLevel,
  q.qmaPartShortDescription,
  q.qmaQuantityPerParent
FROM dbo.QuoteAssemblies q
  inner join openjson(@Arr) ar on ar.[value] = q.qmaQuoteAssemblyID
ORDER BY ar.[key];

If you can't utilise JSON for this task, you will need to somehow produce a rowset with your array elements being correctly numbered, and use it in a similar fashion. There are lots of ways to achieve this, and it doesn't necessarily have to be done on server side. For example, you can create a 2 column key-value user-defined table type in your database, and provide the data as a parameter for your query.

Another approach is to supply the data in the form of XML, something like this:

declare @Ax xml = N'<r>
  <i n="0" v="0" />
  <i n="1" v="1" />
  <i n="2" v="21" />
  ...
</r>';

SELECT q.qmaQuoteAssemblyID,
  q.qmaPartID,
  q.qmaLevel,
  q.qmaPartShortDescription,
  q.qmaQuantityPerParent
FROM dbo.QuoteAssemblies q
  inner join @Ax.nodes('/r/i') ar(c) on ar.c.value('./@v', 'int') = q.qmaQuoteAssemblyID
ORDER BY ar.c.value('./@n', 'int');

Still, the numbering of XML nodes is better to be done by the application, as there is no efficient way to do this on the database side. That, and performance might be rather worse compared to the option 1.

SQL Select Statement with order by as parameter

You need to do it like this instead:

order by    
    case @param
        when 'PutYourColumNameHere' then PutYourColumNameHere
        when 'AnotherColumn' then AnotherColumn--, and so on                    
    end desc

So for example if your table players has 2 columns named Age and Name, then it will look like this:

Select * from players 
order by 
    case @param 
        when 'Age' then Age
        when 'Name' then Name               
    end desc;

SQL Order by the parameter value from scalar-valued function without including it into select

EDIT 3:

I believe this would work. The idea is to first create a subquery defining your sort field and the data you want to retrieve. The second step is to assign a sortorder to every record based on the sort field. This is your primary sort. The next field will assign a 1 to each record in the group and the order by can give additional options as which record to keep in a group if your requirements were more complex.

The outer query does not need distinct or group by as the window functions take care of that. I do not have access to a test environment so the syntax and logic may be off a bit but I think this will work for you.

Select Data, 
   From
      (
       Select Data, 
              Row_Number() Over (Order By SortField) TopLevelSort,
              Row_Number() Over (Partition By Data Order By SortField) Keepers       
         From
             (Select SUBSTRING(dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E), 9, 13) SortField,
                   dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E), 9, 13) Data
                  From dbo.Project P
                  Where --conditions
              ) A
       ) B
    Where Keepers = 1
    Order By TopLevelSort

---

Related Topics