MSSQL, combining LIMIT override and join

Hi experts,

I have the following query wich is working well as an ovverride for the LIMIT used in MySQL:

SELECT * FROM (SELECT row_number() OVER (ORDER BY ID) AS rownum, Name, ID FROM TABLE) AS A WHERE A.rownum BETWEEN $offset AND ($offset+$limit) ORDER BY A.rownum ORDER BY $order_by

I need to join this query with another table (TABLE2) on TABLE.ID/TABLE2.ID and still keep the BETWEEN statement working for limit replacement.

The query needs to be valid for MSSQL2005.

Any ideas?

Thanks
bgpro_noAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should have no issue with joining?
SELECT a.* , ot.*
FROM (SELECT row_number() OVER (ORDER BY ID) AS rownum, Name, ID FROM TABLE) AS A 
JOIN other_table ot
  ON ot.ID = a.ID
WHERE A.rownum BETWEEN $offset AND ($offset+$limit) 
ORDER BY A.rownum ORDER BY $order_by

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bgpro_noAuthor Commented:
Hi,

the query returns each result as many times as it occurs in the other table.

Tried a GROUP BY a.ID, but it throws the following error:

Column 'A.rownum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Any ideas?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>as many times as it occurs in the other table.
well, if there are many rows in the "other" table, which is the condition to determine which one of those rows to take ...

check out this article to see how to solve it, eventually:
http://www.experts-exchange.com/A_3203.html
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.