Jet-SQL to TSQL

Question

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)?

Possible duplicate of [Create a view with ORDER BY clause](http://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause) — Juan Carlos Oropeza, Aug 18 '16 at 13:18
I read more carefully. Im not sure what is your problem. Create a view with `order by` inside or calling a view and include `order by` outside. First wont work as describe on the duplicate question link, second should, but you say doesnt, why any error? — Juan Carlos Oropeza, Aug 18 '16 at 13:22
is confussing because your table has VIEW in the name `[dbo].[VIEW_Teile-LF-Beziehungen]` and your code doesnt include `CREATE VIEW` — Juan Carlos Oropeza, Aug 18 '16 at 13:31

Juan Carlos Oropeza · Answer 1 · 2016-08-18T13:39:25.717

1

ORDER BY doesnt work inside the view. SQL server is free to return the rows anyway he want if you dont include the order by when calling the View

So you need

 SELECT *
 FROM yourView
 ORDER BY yourField

EDIT: Im saying if your view is

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

Then you call your View like this

 SELECT *
 FROM yourView
 ORDER BY [Anz Teile]

edited Aug 18 '16 at 13:39

answered Aug 18 '16 at 13:19

Juan Carlos Oropeza

47,252
12
78
118

That I know of course. My question was more about the difference between Jet- and T-SQL. But thank you :-) – Vegeta_77 Aug 18 '16 at 13:48
Then you should work in a better question. Still not sure why you select that as the correct answer? Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Aug 18 '16 at 13:51

Shnugo · Accepted Answer · 2016-08-18T13:38:22.380

1

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!

edited Aug 18 '16 at 13:38

answered Aug 18 '16 at 13:22

Shnugo

66,100
9
53
114

why add `ROW_NUMBER`? instead just `ORDER BY COUNT([Lieferant])` – Juan Carlos Oropeza Aug 18 '16 at 13:29
@JuanCarlosOropeza Just because the `ROW_NUMBER` can be placed **within** the `VIEW` and would return with a guaranteed sorting (depending on some rules). As stated above and in several other contributions: The best way is the `ORDER BY` on the outermost `SELECT`. – Shnugo Aug 18 '16 at 13:31
My confusion is are you creating a view or select from a view? – Juan Carlos Oropeza Aug 18 '16 at 13:34
@JuanCarlosOropeza, sorry, this is not clear indeed: This was the query **within** the `VIEW`. I'll edit my answer. – Shnugo Aug 18 '16 at 13:35
I dont understand why you say using `row_number` inside the view will return a sorted set? and then say you need use`order by` outside? Because you know SQL doesnt guarantee order on any set. – Juan Carlos Oropeza Aug 18 '16 at 13:42
@Shnugo. Nice I was looking for this kind of solution. Thank you very much. – Vegeta_77 Aug 18 '16 at 13:52
"Many people think, that tables have kind of an implicit order ... but this is random" Not random! Arbitrary, perhaps? The implicit order would be predictable once all the many the variables are known but the point is no one would be bothered to find them out. Randomness also takes effort too :) – onedaywhen Aug 18 '16 at 15:34
@onedaywhen Okay, that's true. In a mathematical / scientific point of view the terminus *random* is wrong. The sort order - is for sure - bound to some complex rules... It's *feels* just random to a poor little db boy, who created a working query and - all of a sudden - gets other results than before. So: Thx for pointing this out :-) – Shnugo Aug 18 '16 at 21:22

Jet-SQL to TSQL

2 Answers2

EDIT

EDIT2

BUT