CODEWITHSUNDEEP
sql-serversql-order-by
Pascal Goldbach
Pascal Goldbach

Reputation: 1007

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

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?

Upvotes: 0

Views: 55

Answers (2)

MPost
MPost

Reputation: 535

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());
    }

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89071

You could legally start every ORDER BY with something like:

order by @@spid

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

Upvotes: 1

Related Questions