I was trying to recently changing my database from MariaDB to SQL server(Unfortunately not my call) on my Laravel web app. However, when I ran and opened one of my pages I got the error:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

And this:

SELECT COUNT(*) AS aggregate
FROM [items]
     INNER JOIN (SELECT [model_name],
                        MAX(version) AS max_version
                 FROM [items]
                 WHERE [items].[deleted_at] IS NULL
                 GROUP BY [model_name]
                 ORDER by [created_at] ASC) [max_version_table] ON [items].[model_name] = [max_version_table].[model_name]
                                                              AND [items].[version] = [max_version_table].[max_version]
WHERE [items].[deleted_at] IS NULL;

I'm pretty new to SQL and here is my Eloquent ORM code:

public function AllModel(){
    $max_version_table = Item::select('model_name', DB::raw('MAX(version) as max_version'))->orderBy('created_at')->groupBy('model_name');

    $items = Item::select('*')->joinSub($max_version_table, 'max_version_table', function($join){
    $join->on('items.model_name','=','max_version_table.model_name');
    $join->on('items.version','=','max_version_table.max_version');
    })->paginate(5);    //Shows max version of each model name

    return view('admin.item.index',compact('items'));
}

My understanding is that MSSql doesn't support OrderBy since this works perfectly on MariaDB. If so may I ask what can I swap it for? Appreciate any help

CodePudding user response：

MSSQL definitely supports order by but in your case as the error already suggested

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

You cannot use order by in subqueries like you have done. Go through the mssql order by documentation for more understanding

And by looking at your query I think its unnecessary to add any order by as it is doing nothing. You can remove the clause.

SELECT count(*) AS AGGREGATE
FROM [items]
INNER JOIN
  (SELECT [model_name],
          MAX(VERSION) AS max_version
   FROM [items]
   WHERE [items].[deleted_at] IS NULL
   GROUP BY [model_name]
) AS [max_version_table] ON [items].[model_name] = [max_version_table].[model_name]
AND [items].[version] = [max_version_table].[max_version]
WHERE [items].[deleted_at] IS NULL

CodePudding user response：

SQL Server definitely supports ORDER BY, but your query should look like

select count(*) as aggregate 
from [items] 
inner join 
(
  select [model_name], MAX(version) as max_version from [items] where [items].      [deleted_at] is null group by [model_name] 
) as [max_version_table] 
on 
 [items].[model_name] = [max_version_table].[model_name] 
    and [items].[version] = [max_version_table].[max_version] 
where [items].[deleted_at] is null