Jet-SQL to TSQL

In MS Access 2010 I have the following code:

SELECT 
    [Teile-LF-Beziehungen].Lieferant, 
    COUNT([Teile-LF-Beziehungen].Lieferant) AS [Anz Teile], 
    First([Teile-LF-Beziehungen].Name) AS Name
FROM
    [Teile-LF-Beziehungen]
GROUP BY 
    [Teile-LF-Beziehungen].Lieferant
ORDER BY 
    COUNT([Teile-LF-Beziehungen].Lieferant) DESC;

I want to put that query into SQL Server, because MS Access should be only the frontend.

But in SQL Server I can't use the ORDER in a view. But why? I don't understand it. The code I want to use in SQL Server:

SELECT  
    [Lieferant],
    COUNT([Lieferant]) AS [Anz Teile],
    MIN([Name]) AS [Name]
FROM
    [dbo].[VIEW_Teile-LF-Beziehungen]
GROUP BY
    [Lieferant]
ORDER BY 
    COUNT([Lieferant]) DESC;

I know it don't work. But is there any way to incur a MS Access query 1:1 to a SQL Server query (view)?

Solution

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 ROW_NUMBER with OVER(ORDER BY). The result is delivered in this order and the order is guaranteed as long the orderby is sorting after unique values.

EDIT

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;

EDIT2

This 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.

BUT

As stated in many places: The best is the outermost ORDER BY in any case!