Missing index recommendations do not take into account cardinality, statistics, etc. when generating column order; they're grouped by equality predicate(s), inequality predicate(s), and include column(s) to possibly produce a covering index (avoiding key lookups). Within each group, the column order is just the order in the table definition, nothing fancy. For example, from the plan XML for this missing index recommendation we have an equality column on Status, inequality columns on TerritoryID and ModifiedDate (in table column order: 13 and 26, respectfully), and includes the RevisionNumber column to cover the SELECT list:
<MissingIndex Database="[AdventureWorks2017]" Schema="[Sales]" Table="[SalesOrderHeader]">
<Column Name="[Status]" ColumnId="6" />
<Column Name="[TerritoryID]" ColumnId="13" />
<Column Name="[ModifiedDate]" ColumnId="26" />
<Column Name="[RevisionNumber]" ColumnId="2" />
We can visualize the content of the recommended index with a query:
SELECT Status, TerritoryID, ModifiedDate, RevisionNumber
ORDER BY Status, TerritoryID, ModifiedDate;
If we implement the recommended index, we can "seek" to 5 for the Status column, but have to read every row where TerritoryID is less than 6 or greater than 6 to evaluate ModifiedDate to determine if it falls within the specified range.
If the column order of the inequality column group is flipped in the index definition, then we get an index that looks like this (technically, the table's clustering key, SalesOrderID, is in there, too):
SELECT Status, ModifiedDate, TerritoryID, RevisionNumber
ORDER BY Status, ModifiedDate, TerritoryID;
CREATE INDEX Status_ModifiedDate_TerritoryID_Includes
ON Sales.SalesOrderHeader (Status, ModifiedDate, TerritoryID)
If we implement this index definition, we can seek to Status of 5 and where the range for ModifiedDate begins within the index. Then, read all of the rows in the specified ModifiedDate range and evaluate the residual predicate on TerritoryID. This allows for fewer pages and rows to be read than with the recommended index definition for this query and its parameters.
Missing index recommendations are narrowly focussed on parts of a query (WHERE clause) and do not take into account things like ORDER BY, GROUP BY, DISTINCT, etc. where column order may allow reading fewer pages or eliminating an expensive sort operation. Don't misunderstand, I really like the feature, but I have found them to be more of a starting point when evaluating index recommendations or performing index tuning. It's Clippy saying, "hey, I got an idea".