Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 4 days ago.
The community is reviewing whether to reopen this question as of 4 days ago.
Improve this question

I have table Estates: [Id, Title] and Results: [EstateId, Type (nvarchar), A (int), B (decimal)] and a query similar to this:

select *
from Estates
order by (
    select A
    from Results
    where Type = 'some dynamic value'
    and EstateId = Id
) ASC, (
    select B
    from Results
    where Type = 'some dynamic value'
    and EstateId = Id
) DESC

The query plan says that both subqueries are run, and I would like to know if there's a way to rewrite it to order by multiple result values from a single subquery.

I have tried concatenating to a string (with leading zeroes for natural sorting), but the ascending/descending part is hard. I have also tried hierarchyid, but it does not support zeroes after the dot (the fractional part of the decimal).

Any good ideas?

This is a dynamically built query, and though I could refactor it to make it join the Results table, it will make the code quite weird in other places because I already join the Results table but with another value of Type. Therefore I hoped for a sub-query solution.