Sin is smooth, yo.

Check me on this, if you would/can.

This is the SQL statement:

select u.lastname, u.firstname, u.company, a.datepaid
from users u
left join assignments a on a.userid = u.id
where a.datepaid >= '2010-01-01'
order by u.lastname, u.firstname, u.company, a.datepaid

I am trying to... paginate, really.  10 records per page.

What I would do in MySQL is append this line to the very end:

limit 10, 10

However, I don't know how to do that in SQL Server.  I know how to include top 10, but not how to do the offset.  The closest I have:

select top 10 u.lastname, u.firstname, u.company, a.datepaid
from users u
left join assignments a on a.userid = u.id
where a.datepaid >= '2010-01-01'
and a.id not in
(
  select top 10 a.id
  from users u
  left join assignments a on a.userid = u.id
  where a.datepaid >= '2010-01-01'
  order by u.lastname, u.firstname, u.company, a.datepaid
)
order by u.lastname, u.firstname, u.company, a.datepaid

Then for each page, add 10 to the top # in the parenthesised code.

That just looks clunky as virtue, though.

Is there a better way of doing this?

Sin is smooth, yo.

Check me on this, if you would/can.

This is the SQL statement:

select u.lastname, u.firstname, u.company, a.datepaid
from users u
left join assignments a on a.userid = u.id
where a.datepaid >= '2010-01-01'
order by u.lastname, u.firstname, u.company, a.datepaid

I am trying to... paginate, really.  10 records per page.

What I would do in MySQL is append this line to the very end:

limit 10, 10

However, I don't know how to do that in SQL Server.  I know how to include top 10, but not how to do the offset.  The closest I have:

select top 10 u.lastname, u.firstname, u.company, a.datepaid
from users u
left join assignments a on a.userid = u.id
where a.datepaid >= '2010-01-01'
and a.id not in
(
  select top 10 a.id
  from users u
  left join assignments a on a.userid = u.id
  where a.datepaid >= '2010-01-01'
  order by u.lastname, u.firstname, u.company, a.datepaid
)
order by u.lastname, u.firstname, u.company, a.datepaid

Then for each page, add 10 to the top # in the parenthesised code.

That just looks clunky as virtue, though.

Is there a better way of doing this?

Originally posted on Dreamwidth.