Need To Use Row_number Without Over In Sqlserver 2005

Possible Duplicate: ROW_NUMBER() without over in SQL I have a grid with sorting on each column and have to show only 50 rows at a time. I am using sql server 2005 as database.

Solution 1:

Use several ROW_NUMBER clauses per column and choose the relevant one:

....
    ROW_NUMBER() OVER (ORDER BY [Column1]) as rn1,
    ROW_NUMBER() OVER (ORDER BY [Column2]) as rn2,
    ROW_NUMBER() OVER (ORDER BY [Column3]) as rn3,
    ROW_NUMBER() OVER (ORDER BY [Column4]) as rn4
....

OR use a CASE expression. Note: all datatypes must be compatible

ROW_NUMBER() OVER (ORDER BY CASE @sort
                     WHEN 1 THEN [Column1]
                     WHEN 1 THEN [Column2]
                     WHEN 1 THEN [Column3]
                     ...
                   END

OR If you really want an arbitrary row number then do this:

ROW_NUMBER() OVER (ORDERBY (SELECT1)) as rn

Solution 2:

I found some way to do it.

SELECTOrderID, CustomerID, EmployeeID, OrderDate, ShippedDate,
  @Offset, @Limit, @SortColumn, @SortDirectionFROMOrdersWHEREROW_NUMBER() OVER 
  (
    ORDER BY
      /* same expression as in the ORDER BY of the whole query */
  ) BETWEEN (@PageNum - 1) * @PageSize + 1AND @PageNum * @PageSize/* AND more conditions ... */ORDERBYCASEWHEN@SortDirection = 'A' THEN
    CASE @SortColumnWHEN'OrderID'    THEN OrderID
      WHEN'CustomerID' THEN CustomerID
      /* more... */
    END
  END,
  CASE WHEN@SortDirection = 'D' THEN
    CASE @SortColumnWHEN'OrderID'    THEN OrderID
      WHEN'CustomerID' THEN CustomerID
      /* more... */
    END 
  END DESC