gistfile1.diff

diff --git a/lib/arel/visitors/sqlserver.rb b/lib/arel/visitors/sqlserver.rb
index 6da7c32..d171755 100644
--- a/lib/arel/visitors/sqlserver.rb
+++ b/lib/arel/visitors/sqlserver.rb
@@ -175,6 +175,7 @@ module Arel
             visit_Arel_Nodes_SelectStatementWithOutOffset(o,true),
           ") AS [__rnt]",
           (visit(o.offset) if o.offset),
+          ("ORDER BY [__rnt].[__rn] ASC" unless active_record_count_subquery?(o))
         ].compact.join ' '
       end
 
@@ -282,7 +283,16 @@ module Arel
         core = o.cores.first
         core.source.right.any? { |x| Arel::Nodes::Join === x }
       end
-
+      
+      def active_record_count_subquery?(o)
+        core = o.cores.first
+        projection = core.projections.first
+        core.projections.size == 1 && 
+          Arel::Nodes::As === projection && 
+          projection.left == '1' && 
+          projection.right == 'count_column'
+      end
+      
       def complex_count_sql?(o)
         core = o.cores.first
         core.projections.size == 1 &&
diff --git a/test/cases/offset_and_limit_test_sqlserver.rb b/test/cases/offset_and_limit_test_sqlserver.rb
index 448682e..8c0f126 100644
--- a/test/cases/offset_and_limit_test_sqlserver.rb
+++ b/test/cases/offset_and_limit_test_sqlserver.rb
@@ -46,7 +46,7 @@ class OffsetAndLimitTestSqlserver < ActiveRecord::TestCase
     end
 
     should 'alter SQL to limit number of records returned offset by specified amount' do
-      sql = %|EXEC sp_executesql N'SELECT TOP (3) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [books].[id] ASC) AS [__rn], [books].* FROM [books] ) AS [__rnt] WHERE [__rnt].[__rn] > (5)'|
+      sql = %|EXEC sp_executesql N'SELECT TOP (3) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [books].[id] ASC) AS [__rn], [books].* FROM [books] ) AS [__rnt] WHERE [__rnt].[__rn] > (5) ORDER BY [__rnt].[__rn] ASC'|
       assert_sql(sql) { Book.limit(3).offset(5).all }
     end
     
diff --git a/test/cases/sqlserver_helper.rb b/test/cases/sqlserver_helper.rb
index 542c7c1..b08b103 100644
--- a/test/cases/sqlserver_helper.rb
+++ b/test/cases/sqlserver_helper.rb
@@ -42,6 +42,7 @@ class StringDefaultsBigView < ActiveRecord::Base ; self.table_name = 'string_def
 class SqlServerNaturalPkData < ActiveRecord::Base ; self.table_name = 'natural_pk_data' ; self.primary_key = 'legacy_id' ; end
 class SqlServerTinyintPk < ActiveRecord::Base ; self.table_name = 'tinyint_pk_table' ; end
 class SqlServerNaturalPkIntData < ActiveRecord::Base ; self.table_name = 'natural_pk_int_data' ; end
+class SqlServerOrderRowNumber < ActiveRecord::Base ; self.table_name = 'order_row_number' ; end
 class SqlServerNaturalPkDataSchema < ActiveRecord::Base ; self.table_name = 'test.sql_server_schema_natural_id' ; end
 class SqlServerQuotedTable < ActiveRecord::Base ; self.table_name = 'quoted-table' ; end
 class SqlServerQuotedView1 < ActiveRecord::Base ; self.table_name = 'quoted-view1' ; end
diff --git a/test/schema/sqlserver_specific_schema.rb b/test/schema/sqlserver_specific_schema.rb
index b22c478..46c514c 100644
--- a/test/schema/sqlserver_specific_schema.rb
+++ b/test/schema/sqlserver_specific_schema.rb
@@ -91,6 +91,23 @@ ActiveRecord::Schema.define do
     t.string :name
   end
   
+  # http://blogs.msdn.com/b/craigfr/archive/2008/03/19/ranking-functions-row-number.aspx
+  execute "IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'order_row_number') DROP TABLE order_row_number"
+  execute <<-ORDERROWNUMBERSQL
+    CREATE TABLE [order_row_number] (id int IDENTITY, a int, b int, c int)
+    CREATE UNIQUE CLUSTERED INDEX [idx_order_row_number_id] ON [order_row_number] ([id])
+    INSERT [order_row_number] VALUES (0, 1, 8)
+    INSERT [order_row_number] VALUES (0, 3, 6)
+    INSERT [order_row_number] VALUES (0, 5, 4)
+    INSERT [order_row_number] VALUES (0, 7, 2)
+    INSERT [order_row_number] VALUES (0, 9, 0)
+    INSERT [order_row_number] VALUES (1, 0, 9)
+    INSERT [order_row_number] VALUES (1, 2, 7)
+    INSERT [order_row_number] VALUES (1, 4, 5)
+    INSERT [order_row_number] VALUES (1, 6, 3)
+    INSERT [order_row_number] VALUES (1, 8, 1)
+  ORDERROWNUMBERSQL
+  
   execute "IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'natural_pk_data') DROP TABLE natural_pk_data"
   execute <<-NATURALPKTABLESQL
     CREATE TABLE natural_pk_data(