StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can move the case expression to an <code>order by</code> clause:</p> <pre><code>order by (case when @SortDirection=1 and CHARINDEX('Name',@SortExpression)=1 then s.name end) desc, (case when @SortDirection=1 and CHARINDEX('StockNumber',@SortExpression)=1 then s.StockNumber end) desc, (case when @SortDirection=1 and (CHARINDEX('StockNumber',@SortExpression)<>1 and CHARINDEX('Name',@SortExpression)<>1) then va.match end) desc, (case when @SortDirection<>1 and CHARINDEX('Name',@SortExpression)=1 then s.name end) asc, (case when @SortDirection<>1 and CHARINDEX('StockNumber',@SortExpression)=1 then s.StockNmber end) asc, (case when @SortDirection<>1 and (CHARINDEX('StockNumber',@SortExpression)<>1 and CHARINDEX('Name',@SortExpression)<>1) then va.match end) asc </code></pre> <p>I notice the expression has <code>va.match</code>, so it doesn't really match any tables in your query. So, I'm just putting in the <code>order by</code> expression.</p> <p>And, yes, as the table gets bigger, this is going to take more time. I don't know that the <code>order by</code> will be more efficient than the <code>row_number()</code>, but it is possible.</p> <p>If you need to order the rows, then you have to do a sort, one way or another (perhaps you could use an index instead). If you don't care about the order, you could take your chances with:</p> <pre><code>row_number() over (order by (select NULL)) </code></pre> <p>In SQL Server, I have found that this assigns a sequential number without a separate sort. However, this is not guaranteed (I haven't found any documentation to support this use). And, the result is not necessarily stable from one run to the next.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload