Databases
StackOverflow2013
Postsdbo
POSQL Server top with ties for both top and bottom
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server top with ties for both top and bottom
    primarykey
    data
    text
    <p>I'm looking for a more efficient way to implement my paging in sql server 2008.</p> <p>I need to retrieve result set rows @from to @to, ordered by score value, but I also need to retrieve all rows immediately before and after @from and @to that match the score value of those specific @from and @to rows. For example this could be like either of the two following queries:</p> <p>N.b., score values are not unique, are not indexed, and are not provided to the rest of the query in a pre-sorted form.</p> <p>a) (actually, I don't think this first example is guaranteed to give the results I need, because I don't think the order by in the 2 subqueries is guaranteed to produce an identical ordering. But for the sake of understanding what I would like a query syntax to do, consider only for this example that the order-by in the subqueries are identical)</p> <pre><code>select * from ( select top (@to) * with ties from result_set order by score desc ) tt union select from ( select top (@to - @from + 1) * with ties from ( select top (@to) * result_set order by score desc ) tt order by score asc ) tt order by score desc </code></pre> <p>or </p> <p>b)</p> <pre><code>/*EDIT: nested the rank() window function and related where criteria one level deeper than the count(*), as this gave me significant (25%) decrease in runtime with my data, and just kinda makes sense.*/ select score from ( select score, myrank, count(*) over (partition by score) frequency from ( select score, rank() over (order by score desc) myrank from result_set ) tt where myrank &lt;= @to ) tt where @from &lt;= myrank + frequency - 1 order by score desc </code></pre> <p>I get the results I need using syntax (b) in combination with the following test CTE: </p> <pre><code>with result_set (score) as ( select 0.5 union all select 0.5 union all select 0.2 union all select 0.1 union all select 0.55 union all select 0.5 union all select 0.1 ) </code></pre> <p>But the partitioned window function requires 2 nested loops and 3 lazy spool operators. Is there a more efficient syntax for this?</p> <p>Here is the current actual execution plan: <img src="https://i.stack.imgur.com/nbl9w.png" alt="enter image description here"></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJM Hicks
    1. USJM Hicks
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server top with ties for both top and bottom
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server top with ties for both top and bottom
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server top with ties for both top and bottom
      1. This table or related slice is empty.
      1. VTUpMod
    1. COmy goal is to see if there's something that can be done that doesn't assume any indexing at all on the score values, any pre-sort of the score values, or any use of values from a previous execution of the query, e.g. I can't know what was the lowest score value from the last time the query was run.
      singulars
      1. POSQL Server top with ties for both top and bottom
      1. USJM Hicks
    2. COideally it seems that the execution of the window-function query would not necessarily require spools and joins. I imagine there might be a newer window function syntax in SQL Server 2012 that might just do the same thing a little bit better than my 2008 is yet doing. But I'd like to get there with what I have available for now in 2008 developer or enterprise edition.
      singulars
      1. POSQL Server top with ties for both top and bottom
      1. USJM Hicks
    3. COI haven't looked at your question in any detail but that's the plan shape you always get for windowed aggregates. It is only one spool that appears in the plan three times. More details about the [Common Subexpression Spool](http://sqlblog.com/blogs/paul_white/archive/2010/07/28/partitioning-and-the-common-subexpression-spool.aspx) here
      singulars
      1. POSQL Server top with ties for both top and bottom
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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