Custom sorting in SQL order?

Question

Here is the situation I'm trying to solve:

I have a query that can return a recordset. A field that is sorted can have several different values ​​- for this question we will say that this value can be A, B, C, D, E or Z

Now, depending on the results of the query, sorting should behave as follows: If only AE records are found, sorting them is "naturally" in order. But if there is a Z record in the results, then this should be the first result in the query, but the rest of the records should be in the "natural" sort order.

For example, if an ACD is found, then the result should be

A C D 

But if ABDEZ is found, the result should be sorted:

 Z A B D E 

Currently, the request looks like this:

 SELECT NAME, SOME_OTHER_FIELDS FROM TABLE ORDER BY NAME 

I know that I can code the sort function to do what I want, but due to the way I use the results, I cannot use it because the results are processed by a third-party library, to which I am just passing the SQL query. Then it processes the results, and there seems to be no binding for sorting the results and just passing the results to the library. He must execute the SQL query himself, and I do not have access to the source code of the library.

So, for all of you SQL gurus, can you provide a query for me that will do what I want?

Answer 1

How do you identify the entry Z? What makes him different? Once you understand this, add it to the ORDER BY clause.

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

Thus, only the record Z will correspond to the first order, and all other records will be sorted by second-order sorting (name). You can exclude second-order sorting 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 did not specify which database you are using.

Answer 2

Not sure which DB you are using - for Oracle: to work:

 SELECT NAME, SOME_OTHER_FIELDS, DECODE (NAME, 'Z', '_', NAME ) SORTFIELD FROM TABLE ORDER BY DECODE (NAME, 'Z', '_', NAME ) ASC 

Answer 3

There are several ways to solve this problem, and the best solution depends on a number of factors that you are not discussing, such as the nature of these A..Z values ​​and which database product you are using.

If you have only one value that needs to be sorted on top, you can ORDER BY expression that matches this value with the smallest possible sort value (with CASE or IIF or IFEQ , depending on your database).

If you have several different special sort values, you could ORDER BY more complex expression, or you could UNION several SELECT s together, with one SELECT for default sorting and an additional SELECT for each special value. SELECT will include a sort column.

Finally, if you have multiple values, you can put the sort values ​​in a separate table and JOIN this table in your query.