In MS Access 2010 I have the following code:
COUNT([Teile-LF-Beziehungen].Lieferant) AS [Anz Teile],
First([Teile-LF-Beziehungen].Name) AS Name
COUNT([Lieferant]) AS [Anz Teile],
MIN([Name]) AS [Name]
Only the outermost select can use an order by (but you might state a
TOP 100 percent to trick this out). Therefore it is perfectly OK, that at VIEW does not allow this.
Many people think, that tables have kind of an implicit order (as you see the result ordered), but this is random... The next call could lead to a different sorting.
There is another way using
OVER(ORDER BY). The result is delivered in this order and the order is guaranteed as long the orderby is sorting after unique values.
Sorry my first attempt was to quick. The
ROW_NUMBER was not allowed due to the grouping
This should work:
SELECT tbl.Lieferant ,tbl.[Anz Teile] ,tbl.Name ,ROW_NUMBER() OVER(ORDER BY tbl.[Anz Teile] DESC) AS Sort FROM ( SELECT [Lieferant] ,COUNT([Lieferant]) AS [Anz Teile] ,MIN([Name]) AS [Name] FROM [dbo].[VIEW_Teile-LF-Beziehungen] GROUP BY [Lieferant] ) AS tbl;
SELECT can be placed within a
VIEW, just place your
CREATE VIEW YourViewName AS before the
SELECT and execute. After this you'll be able to do a
SELECT * FROM YourViewName to get a sorted list.
As stated in many places: The best is the outermost
ORDER BY in any case!