0

I am new to SQL Server 2008 R2 and got stuck in the following situation. I am trying to join two tables using inner join and addi limits and offset to the same.

Since MySQL's syntax is way different than SQL Server's, I am unable to get the result. I used this link but it didn't help me out in any way. Any help would be appreciated. Thanks!

Community
  • 1
  • 1
Ronak Rathod
  • 430
  • 4
  • 14
  • Sql server 2008 r2 does not have a built in option for limit and offset, that was introduced in 2012 version as "offset...fetch next". If you will edit your question to include the relevant tables DDL, some sample data (prefrebly as DML) and desired results, It will be a lot easier to help you. – Zohar Peled Dec 03 '16 at 12:11
  • Does the top answer here answer your question? http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server – mendosi Dec 03 '16 at 12:11
  • @mendosi top is working well for single table, but failed to create a query for Inner join of tables.... – Ronak Rathod Dec 03 '16 at 16:33

1 Answers1

2

You can use this query:

SET ROWCOUNT x -- x is Rows Count to get For Example : 50 
SELECT t.*
FROM (
      SELECT row_number() over (ORDER BY a.id ) AS rowindex, a.*
      FROM table_1 a INNER JOIN table_2 b ON a.id = b.aid
      WHERE -- limit conditions For Example : A.title = 'name2'
     ) AS t
WHERE t.rowindex >= y -- Y is Start index for Offset

good luck

TT.
  • 15,774
  • 6
  • 47
  • 88
Mehdi Moshiri
  • 141
  • 3
  • @TT/@Mehdi Moshiri Thank you for ur response. Before trying i would like to know, what is 't' in the above query? – Ronak Rathod Dec 03 '16 at 16:32
  • @RonakRathod It's a table alias, in this case for the derived table in the from clause. If you want to reference a column in that derived table, you do that by qualifying it with the table alias. In the statement in the answer, every column is selected by the `t.*`; the `*` means all columns, the `t.` stands for the derived table aliassed by `t`. – TT. Dec 03 '16 at 16:34
  • @TT. in my sql server i am getting an error is "The column prefix 't' does not match with table name or alias name used in the query". Can we fix this ? since i also cannot see the alias t has been defined in the query... – Ronak Rathod Dec 03 '16 at 16:39
  • @TT. sorry I Missed that "AS t". Thank you for the answer. It is working like charm.... – Ronak Rathod Dec 03 '16 at 16:42