gistfile1.sql

/* Here is the start of the assocaiation limiting SQL before needing limit/offset */

SELECT [posts].id 
FROM [posts]  
LEFT OUTER JOIN [authors] ON [authors].id = [posts].author_id 
WHERE (authors.name = 'David') 
GROUP BY [posts].id 
ORDER BY MIN(UPPER(posts.title)), MIN(posts.id)

/* Here is my basic way of adding row_numbers to the select */

DECLARE @row_number TABLE (row int identity(1,1), id int)
INSERT INTO @row_number (id)
  SELECT [posts].id
  FROM [posts]  
  LEFT OUTER JOIN [authors] ON [authors].id = [posts].author_id 
  WHERE (authors.name = 'David') 
  GROUP BY [posts].id 
  ORDER BY MIN(UPPER(posts.title)), MIN(posts.id)
SELECT * FROM @row_number 

/* Here is the final SQL used to get limit and offset IDs for 
association limiting that needs it. The NOCOUNT stuff is important 
because it keeps DBI::ODBC from thinking the INSERT is what it 
needs to collect row information about. */

SET NOCOUNT ON
DECLARE @row_number TABLE (row int identity(1,1), id int)
INSERT INTO @row_number (id)
  SELECT [posts].id
  FROM [posts] 
  LEFT OUTER JOIN [authors] ON [authors].id = [posts].author_id 
  WHERE (authors.name = 'David') 
  GROUP BY [posts].id 
  ORDER BY MIN(UPPER(posts.title)), MIN(posts.id)
SET NOCOUNT OFF
SELECT id FROM (
  SELECT TOP 2 * FROM (
    SELECT TOP 3 * FROM @row_number ORDER BY row
  ) AS tmp1 ORDER BY row DESC
) AS tmp2 ORDER BY row

/* Just so you can see how it is put/works in the total_rows count */

SELECT count(*) as TotalRows from (
  SELECT TOP 1000000000 [posts].id
  FROM [posts]  
  LEFT OUTER JOIN [authors] ON [authors].id = [posts].author_id 
  WHERE (authors.name = 'David') 
  GROUP BY [posts].id 
  ORDER BY MIN(UPPER(posts.title)), MIN(posts.id)
) tally