Custom Sorting in SQL Order by Clause

SQL Custom Order By Clause

Inside of a CASE, you may ascribe a numeric value to each and order those ascending. If you will need to query a large table, consider adding an index on Description to improve sorting performance.

ORDER BY
  CASE 
    WHEN Description = 'Gold - owned' THEN 0
    WHEN Description = 'Silver - identified / offered' THEN 1
    WHEN Description = 'Bronze - no land' THEN 2
    ELSE 99 /* Any other value (which you should not have) sorts after all */
  END ASC  /* And don't forget to be explicit about ASC order though it's the default */

Since this works like a normal column in the ORDER BY, if you needed to then sort by the Amount or other column, it can be appended with a comma.

ORDER BY 
  CASE
    WHEN Description = 'Gold '...
  END ASC,
  Amount DESC,
  AnotherColumn ASC

Custom Sorting in SQL order by clause?

How do you identify the Z record? What sets it apart? Once you understand that, add it to your ORDER BY clause.

SELECT name, *
FROM [table]
WHERE (x)
ORDER BY
    (
     CASE
       WHEN (record matches Z) THEN 0
       ELSE 1
     END
    ),
    name

This way, only the Z record will match the first ordering, and all other records will be sorted by the second-order sort (name). You can exclude the second-order sort if you really don't need it.

For example, if Z is the character string 'Bob', then your query might be:

SELECT name, *
FROM [table]
WHERE (x)
ORDER BY
    (
     CASE
       WHEN name='Bob' THEN 0
       ELSE 1
     END
    ), name

My examples are for T-SQL, since you haven't mentioned which database you're using.

Custom ORDER BY Function SQL

One option is a CTE:

with ordering as (
      select v.*
      from (values(1, 'val1'), (2, 'val2'), (3, 'val3')) v(priority, val)
    )
select e.*
from example e
order by (select priority from ordering o where o.val = e.name);

I would discourage you from doing an explicit join, because that could affect the semantics of the query. A join filters the records before the select, where, group by, and having clauses are processed (logically). The order by only uses results in the result set (logically).

You can, of course, store the CTE values in a temporary table or table variable. That might be what you are after.

Another approach is a bit of a hack, but works under some circumstances. You can replace the logic with:

order by charindex('[' + name + ']', '[name1][name2][name]')

This assumes that all names are in the list. And that names don't have the delimiter characters. This doesn't directly solve your problem, but you can store the string as a variable in a code block or even as a computed column in a table.

SQL Server Custom sorting in ORDER BY clause

SELECT B.ID, 'B' AS Name, 1 as ExplicitOrderGroup
FROM tableB inner join table a inner join table c
UNION
SELECT A.ID, 'A' AS NAME, 0 as ExplicitOrderGroup
FROM tableA inner join table b inner join table c
UNION
SELECT C.ID, 'C' AS NAME, 1 as ExplicitOrderGroup
FROM tableC inner join table a inner join table b
ORDER BY ExplicitOrderGroup, Name

Note, UNION (which behaves a bit like DISTINCT) will not collaps now same rows with different ExplicitOrderGroup. You have to determine whethere it's OK or you need some other behaviour.

Creating custom order by SQL Server

something like this.

order by case when Jack then 1 
          when Apple then 2 
          when Orange then 3 
          ...
         End

Custom Sort Order in CTE

You need to sort the outer query.

Sorting a subquery is not allowed because it is meaningless, consider this simple example:

WITH CTE AS
(    SELECT  ID
     FROM (VALUES (1), (2)) AS t (ID)
     ORDER BY ID DESC
 )
 SELECT *
 FROM CTE
 ORDER BY ID ASC;

The ordering on the outer query has overridden the ordering on the inner query rendering it a waste of time.

It is not just about explicit sorting of the outer query either, in more complex scenarios SQL Server may sort the subqueries any which way it wishes to enable merge joins or grouping etc. So the only way to guarantee the order or a result is to order the outer query as you wish.

Since you may not have all the data you need in the outer query, you may would probably need to create a further column inside the CTE to use for sorting. e.g.

WITH ctedivisiondesc AS
(
    SELECT  *
    FROM    (   SELECT  DH1.ID_NUM,
                        DH1.DIV_CDE,
                        DDF.DEGREE_DESC AS DivisionDesc,
                        DTE_DEGR_CONFERRED,
                        ROW_NUMBER() OVER (PARTITION BY ID_NUM ORDER BY DTE_DEGR_CONFERRED DESC) AS [ROW NUMBER],
                        CASE 
                           WHEN DDF.DEGREE_DESC = 'Certificate' THEN 1
                           WHEN DDF.DEGREE_DESC = 'Undegraduate' THEN 2
                           WHEN DDF.DEGREE_DESC = 'Graduate' THEN 3
                           WHEN DDF.DEGREE_DESC = 'Doctor of Chiropractic' THEN 4
                           ELSE 5
                         END AS SortOrder
                FROM    TmsePrd.dbo.DEGREE_HISTORY AS DH1
                        INNER JOIN TmsePrd.dbo.DEGREE_DEFINITION AS DDF
                            ON DH1.DEGR_CDE = DDF.DEGREE

            ) AS t
    WHERE   t.[ROW NUMBER] <= 1
)
SELECT  ID_NUM,
        DIV_CDE,
        DivisionDesc,
        DTE_DEGR_CONFERRED
FROM    ctedivisiondesc
ORDER BY SortOrder;

Order by and custom sorting in Microsoft SQL Server

You can use isNumeric().

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) ELSE 2147483647 END
,   Description

-- for a descending order you can use the maths idea that -1 * a number maintains magnitude but reverses the order ...

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) *     -1  ELSE -2147483648 END
,   Description desc

-- with the extra test case for 12345

CREATE TABLE [dbo].[TEST]
(
    [Tag] [nvarchar](max) NULL,
    [Category] [nvarchar](max) NULL,
    [LE] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [Row_Id] [int] NOT NULL,

    CONSTRAINT [PK_testsirius_TEST_0_Row_Id] 
       PRIMARY KEY CLUSTERED ([Row_Id] ASC)
)

Insert into TEST values (1,'Area','EMR','A',199)
Insert into TEST values (2,'Area','EMR','B',200)
Insert into TEST values (3,'Area','EMR','C',201)
Insert into TEST values (201,'Area','EMR','1',399)
Insert into TEST values (202,'Area','EMR','2',400)
Insert into TEST values (203,'Area','EMR','3',401)
Insert into TEST values (204,'Area','EMR','12345',402)

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) ELSE 2147483647 END
,   Description

-- example output

Tag Category    LE  Description Row_Id
201 Area    EMR 1   399
202 Area    EMR 2   400
203 Area    EMR 3   401
204 Area    EMR 12345   402
1   Area    EMR A   199
2   Area    EMR B   200
3   Area    EMR C   201

-- descending order

select * 
from TEST
order by CASE WHEN isNumeric(Description) = 1 THEN Cast([Description] as int) * -1  ELSE -2147483648 END
,   Description desc

-- example output

Tag Category    LE  Description Row_Id
3   Area    EMR C   201
2   Area    EMR B   200
1   Area    EMR A   199
204 Area    EMR 12345   402
203 Area    EMR 3   401
202 Area    EMR 2   400
201 Area    EMR 1   399

How to define a custom ORDER BY order in mySQL

MySQL has a handy function called FIELD() which is excellent for tasks like this.

ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID

Note however, that

1. It makes your SQL less portable, as other DBMSs might not have such function

2. When your list of languages (or other values to sort by) gets much longer, it's better to have a separate table with sortorder column for them, and join it to your queries for ordering.

SQL Order By in oracle custom order by

One method is to use case:

order by (case when firstname = 'nexus' then 1
               when firstname = 'samsumg' then 2
               when firstname = 'apple' then 3
          end)

Another method that has less typing:

order by instr(',nexus,samsung,apple,', ',' || name || ',')

---

Related Topics