SQL error with order in subquery

Question

I am working with SQL Server 2005.

My request:

SELECT ( SELECT COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id ORDER BY refKlinik_id ) as dorduncuay 

And the error:

The ORDER BY clause is not valid in views, built-in functions, retrieved tables, subqueries, and general table expressions, unless TOP or FOR is also specified XML.

How can I use ORDER BY in a subquery?

Answer 1

This is the error you get (my attention):

The ORDER BY clause is not valid in views, built-in functions, derived tables, subqueries, and a general expression table , unless TOP or FOR XML are also specified.

So how can you avoid the mistake? If you specify TOP, I would suggest that this is one of the possibilities.

 SELECT ( SELECT TOP 100 PERCENT COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id ORDER BY refKlinik_id ) as dorduncuay 

Answer 2

Also, that order does not seem to make sense in your request. To use order in the selection, you need to use TOP 2147483647.

 SELECT ( SELECT TOP 2147483647 COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id ORDER BY refKlinik_id ) as dorduncuay 

I understand that "TOP 100 PERCENT" no longer orders gurantee since SQL 2005:

In SQL Server 2005, ORDER BY in the definition definition is used only to identify the rows that are returned by the TOP clause. The BY order does not guarantee the order results when the request is requested, unless ORDER BY is also indicated in the request itself.

See SQL Server 2005 breaks changes

Hope this helps, Patrick

Answer 3

If you are running SQL Server 2012 or later, this is now easy to fix. Add offset 0 rows :

 SELECT ( SELECT COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id ORDER BY refKlinik_id OFFSET 0 ROWS ) as dorduncuay 

Answer 4

You do not need order in your subquery. Move it to the main query and include the column you want to order in the subquery.

however, your query just returns a counter, so I don't see the point of order.

Answer 5

Add the top command to your extra query ...

 SELECT ( SELECT TOP 100 PERCENT COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id ORDER BY refKlinik_id ) as dorduncuay 

:)

Answer 6

In this example, the ordering does not add any information - the COUNT of the set is the same as any order in it!

If you have selected something that depends on the order, you will need to make one of the error messages - use TOP or FOR XML

Answer 7

A subquery (subview), since you have it, returns a dataset that you can then order in your calling query. Ordering the subquery itself will not be a (reliable) difference with the order of the results in your calling request.

As for SQL itself: a) I did not see a reason for ordering, since you are returning a single value. b) In any case, I see no reason for the additional request, since you are returning only one value.

I guess there is a lot more information here so you can tell us to fix this problem.

Answer 8

Maybe this trick will help someone

 SELECT [id], [code], [created_at] FROM ( SELECT [id], [code], [created_at], (ROW_NUMBER() OVER ( ORDER BY created_at DESC)) AS Row FROM [Code_tbl] WHERE [created_at] BETWEEN '2009-11-17 00:00:01' AND '2010-11-17 23:59:59' ) Rows WHERE Row BETWEEN 10 AND 20; 

here is the internal subquery ordered by created_at field (can be any of your table)

Answer 9

Try moving the order by manufacturer outside the selection and add the order by field in the selection

 SELECT * FROM (SELECT COUNT(1) ,refKlinik_id FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id) as dorduncuay ORDER BY refKlinik_id 

Answer 10

For me, this solution works fine:

 SELECT tbl.a, tbl.b FROM (SELECT TOP (select count(1) FROM yourtable) a,b FROM yourtable order by a) tbl 

Answer 11

If you are creating a temporary table, move the ORDER BY clause from the temp table code code to the external one.

Is not allowed:

 SELECT * FROM ( SELECT A FROM Y ORDER BY YA ) X; 

Allowed:

 SELECT * FROM ( SELECT A FROM Y ) X ORDER BY XA; 

Answer 12

good afternoon

for some guys, the order in the subquery is dubious. sub-query ordering is mandatory if you need to delete some records based on some sort. like

 delete from someTable Where ID in (select top(1) from sometable where condition order by insertionstamp desc) 

so that you can delete the last table of insertion forms. There are three ways to actually do this removal.

however, in many cases, the order in the subquery can be used.

for deletion methods that use the order in the subquery overview below the link

http://web.archive.org/web/20100212155407/http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx

I hope this helps. Thank you all

Answer 13

I use this code to get a second paycheck

I get an error too

The ORDER BY clause is not valid in views, built-in functions, views, subqueries, and common table expressions unless TOP or FOR XML is specified.

TOP 100 I used to avoid mistakes

select * from (select tbl.Coloumn1, CONVERT (varchar, ROW_NUMBER () OVER (ORDER BY (SELECT 1))) AS Rowno from (select top 100 * from table 1 by Coloumn1 desc) as tbl) as tbl, where tbl.Rowno = 2

Answer 14

For a simple count, as the OP shows, Order by is strictly not needed. If they use the result of the subquery, it could be. I am working on a similar problem and got the same error in the following query:

- I want the rows from the cost table with the updated date equal to the maximum updated date:

  SELECT * FROM #Costs Cost INNER JOIN ( SELECT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime FROM #HoldCosts cost GROUP BY Entityname, costtype ORDER BY Entityname, costtype -- *** This causes an error*** ) CostsMax ON Costs.Entityname = CostsMax.entityname AND Costs.Costtype = CostsMax.Costtype AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime ORDER BY Costs.Entityname, Costs.costtype 

- *** There are several options for this:

- Add an extraneous TOP clause, it looks like a hack:

  SELECT * FROM #Costs Cost INNER JOIN ( SELECT TOP 99.999999 PERCENT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime FROM #HoldCosts cost GROUP BY Entityname, costtype ORDER BY Entityname, costtype ) CostsMax ON Costs.Entityname = CostsMax.entityname AND Costs.Costtype = CostsMax.Costtype AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime ORDER BY Costs.Entityname, Costs.costtype 

- **** Create a temporary table for order maxCost

  SELECT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime INTO #MaxCost FROM #HoldCosts cost GROUP BY Entityname, costtype ORDER BY Entityname, costtype SELECT * FROM #Costs Cost INNER JOIN #MaxCost CostsMax ON Costs.Entityname = CostsMax.entityname AND Costs.Costtype = CostsMax.Costtype AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime ORDER BY Costs.Entityname, costs.costtype 

Other possible workarounds may be CTE or table variables. But each situation requires you to determine what is best for you. I tend to look primarily at the temporary table. For me it is clear and understandable. YMMV.

Answer 15

For possible needs, you can order a subquery when you have UNION:

You generate a phone book for all teachers and students.

 SELECT name, phone FROM teachers UNION SELECT name, phone FROM students 

First you want to display it with all the teachers, and then with all the students. Therefore, you cannot apply global order.

One solution is to enable the key for first-order enforcement, and then sort the names:

 SELECT name, phone, 1 AS orderkey FROM teachers UNION SELECT name, phone, 2 AS orderkey FROM students ORDER BY orderkey, name 

I think his path is more understandable than the fake compensatory result of the subquery.