It might be a weird scenario from a parallel universe, but in infinite dimensions it is bound to happen to someone else besides me. If you’re getting results from a query where you know some columns by name and others only by their ordinal position (e.g. if SQL is built dynamically and can change based on different condition) – you add sorting to that query by both ordinal position of a column and column name and even throw an expression into the mix. Allow me to illustrate:

Let’s get back to basics, namely Northwind database. If I ran a query like this:

SELECT * FROM Customers

I will get a result, similar to this:

Now to the weird part. Let’s say I need to sort this query by by Contact’s title and Contact’s last name. I don’t know what the column name for the Contact’s title will be, but I know if will always be a static 4th column. I don’t know where in the resultset Contact’s full name be located (it could be 3rd, or 8th or whatever column) but I do know it will always be called ContactName.

There query for this scenario would be:

SELECT * FROM Customers
ORDER BY 4, RIGHT(ContactName, LEN(ContactName) - CHARINDEX(' ', ContactName))

Here we first sort by the 4th column (ordinal position for column with Contact’s title and unknown name) and then by a string expression that extracts last name from ContactName column. The result: