Differences in old and new pagings in SQL Server

I’ve added SQL SERVER 2011 support to Rails ActiveRecord adapter.
Main thing that I added was new paging.
(more about it here http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx)

I want to describe several problems that occurred while I implemented this.

First difference that ordering is necessary when paging is used.

Second difference is that offset must be used always, while limit isn’t necessary.

Third one is difference in limit 0.
select top(0) * from some_table
will return a list of columns.

select * from some_table order by id OFFSET 0 ROWS FETCH FIRST 0 ROWS ONLY
will raise error.

Forth difference with ordering with grouping.
For example: there are posts and comments tables.
We want select only 5 after latest posts where last comments was left.

select top(5) distinct id
from (
select posts.id, ROW_NUMBER() OVER (ORDER BY comments.posted_at) as rn_id
from posts left join comments on comments.post_id=posts.id) help_query
where help_query.rn_id > 1

In “Denali” we use only one query.
select distinct posts.id
from posts left join comments on comments.post_id=posts.id
order by comments.posted_at

is wrong. Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

OK. Lets do standard trick with grouping.

select posts.id
from posts left join comments on comments.post_id=posts.id
group by posts.id
order by max(comments.posted_at)

It is almost right. But now null values on first place.

So last variant is:
select posts.id
from posts left join comments on comments.post_id=posts.id
group by posts.id
order by CASE WHEN (max(comments.posted_at) is null) then 1 else 0 end DESC, max(comments.posted_at)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s