Generating an "ORDER BY" statement, in a more convenient way

Question

In SQL we can use the "WHERE 1=1 hack" to easily generate a WHERE statement in a loop, so we don't need to check if the current iteration is the first one.

without WHERE 1 :

//I  haven't tried the C++ code below, it's just an example to briefly explain the "hack"

string statement = "WHERE";
for (int i = 0 ; i < list.size() ; i++)
{
   if (i != 0)
   {
      statement += " AND "; //we don't want to generate "WHERE AND"
   }
   statement += list[i];
}

the generated statement :

WHERE <something_1>
  AND <something_2>
  AND <something_3>

with WHERE 1 :

string statement = "WHERE 1 = 1"; // Added "1 = 1"
for (int i = 0 ; i < list.size() ; i++)
{
   statement += "AND" + list[i];
}

the generated statement :

WHERE 1 = 1
  AND <something_1>
  AND <something_2>
  AND <something_3>

My issue : I need to generate an "ORDER BY" statement, and I was wondering if such a hack also exists for the ORDER BY statement. I could check if the current iteration in the loop is the last one, but there's maybe a better solution.

ORDER BY a DESC,
   b DESC,
   c DESC,
   d DESC,
   <dummy statement added at the end, so I don't need to remove the last comma>

From what I've read I cannot use "ORDER BY 1", so does a similar hack actually exists?

Why `ORDER BY 1 desc, 2 desc, 3 desc, 4 desc` is not acceptable? MS SQL Server allow for such ordering. — Renat, Aug 26 '19 at 15:43
@Renat an int literal in ORDER BY sorts by the column in ordinal position. — David Browne - Microsoft, Aug 26 '19 at 15:44

score 1 · Accepted Answer · answered Aug 26 '19 at 15:46

1

You could legally start every ORDER BY with something like:

order by @@spid

But it's likely to raise questions about query execution efficiency.

answered Aug 26 '19 at 15:46

David Browne - Microsoft

80,331
6
39
67

MPost · Answer 2 · 2019-08-26T16:02:52.943

What you said about not using "1" is only partially true. If you did a similar pattern, adding "1" at the end would work as long as whatever column is in position 1 isn't also referenced in the order by clause.

I generally do something like this:

string whereClause = (list.Count > 0) 
                        ? "WHERE (" + list.StringJoin(") AND (") + ")"
                        : "";

where StringJoin is a very simple extension method I wrote. Note the added parentheses so that if any of the list elements have an "OR" you don't get into trouble.

Something identical can be done for ORDER BY, just replacing the word WHERE above.

string orderClause = (list.Count > 0) 
                        ? "ORDER BY " + list.StringJoin(", ")
                        : "";

This is C# instead of c++, but a simple version of the extension method is this:

    public static string StringJoin(this IEnumerable<string> list, string separator)
    {
        if (list == null)
            return null;
        else
            return string.Join(separator, list.ToArray());
    }

Generating an "ORDER BY" statement, in a more convenient way

2 Answers2