ORDER BY elements should appear in the selection list if SELECT DISTINCT is specified

Question

I added the columns in the select list to the list by list, but it still gives me an error:

ORDER BY elements should appear in the selection list if the SELECT DISTINCT parameter is specified.

This is where proc is stored:

CREATE PROCEDURE [dbo].[GetRadioServiceCodesINGroup] @RadioServiceGroup nvarchar(1000) = NULL AS BEGIN SET NOCOUNT ON; SELECT DISTINCT rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService FROM sbi_l_radioservicecodes rsc INNER JOIN sbi_l_radioservicecodegroups rscg ON rsc.radioservicecodeid = rscg.radioservicecodeid WHERE rscg.radioservicegroupid IN (select val from dbo.fnParseArray(@RadioServiceGroup,',')) OR @RadioServiceGroup IS NULL ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService END 

Answer 1

Try the following:

 ORDER BY 1, 2 

OR

 ORDER BY rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService 

Answer 2

Although not the same thing, in a sense, DISTINCT implies GROUP BY , because each DISTINCT can be rewritten using GROUP BY . With this in mind, it makes no sense to order something that is not part of the overall group.

For example, if you have a table like this:

 col1 col2
 ---- ----
  eleven
  12
  2 1
  2 2
  2 3
  3 1

and then try to query it like this:

 SELECT DISTINCT col1 FROM [table] WHERE col2 > 2 ORDER BY col1, col2 

This does not make sense, because there may be several col2 values ​​in a row. Which one should I use to order? Of course, in this query, you know that the results will not be the same, but the database server cannot know about this in advance.

Now your case is a little different. You have included all the columns from the order by clause of the select clause, and so at first glance it might seem that they are all grouped. However, some of these columns were included in the calculated field. When you do this in combination with various, distinct directives can only be applied to the final results of calculations: it knows nothing more about the source of the calculations.

This means that the server does not really know that it can count on these columns anymore. He knows that they were used, but he does not know if the calculation operation can cause an effect similar to my first simple example above.

So now you need to do something else to tell the server that the columns can be used for ordering. There are several ways to do this, but this approach should work well:

 SELECT rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService FROM sbi_l_radioservicecodes rsc INNER JOIN sbi_l_radioservicecodegroups rscg ON rsc.radioservicecodeid = rscg.radioservicecodeid WHERE rscg.radioservicegroupid IN (SELECT val FROM dbo.fnParseArray(@RadioServiceGroup,',')) OR @RadioServiceGroup IS NULL GROUP BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService 

Answer 3

Try one of the following:

• Use a column alias:

  ORDER BY RadioServiceCodeId, RadioService

• Use the column layout:

  ORDER BY 1,2

You can order only by columns that actually appear as a result of the DISTINCT query - the basic data is not available for ordering.

Answer 4

Distinct and Group By usually do the same for different purposes ... Both of them create a "working" table in memory based on the columns that are grouped (or selected in the Select Distinct clause) - and then populate this worksheet as the request reads data, adding a new "row" only when the values ​​indicate the need to do this ...

The only difference is that in group “B” there are additional “columns” in the worksheet for any calculated full fields, such as Sum (), Count (), Avg (), etc., which need to be updated for each The original string is read. Distinct does not need to do this ... In the special case, when you Group By only to get individual values ​​(and also there are no aggregate columns in the output), then this is probably the exact same query plan .... It would be interesting to look at query execution plan for two parameters and see what he did ...

Of course, Distinct is a way of reading if that is what you are doing (when your goal is to eliminate duplicate rows and you are not calculating any columns of the population)

Answer 5

When you define concatenation, you need to use ALIAS for the new column if you want to order it in conjunction with DISTINCT Some Ex with sql 2008

 --this works SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName from SalesLT.Customer c order by FullName --this works too SELECT DISTINCT (c.FirstName + ' ' + c.LastName) from SalesLT.Customer c order by 1 -- this doesn't SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName from SalesLT.Customer c order by c.FirstName, c.LastName -- the problem the DISTINCT needs an order on the new concatenated column, here I order on the singular column -- this works SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName, CustomerID from SalesLT.Customer c order by 1, CustomerID -- this doesn't SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName from SalesLT.Customer c order by 1, CustomerID 

Answer 6

You can try the subquery:

 SELECT DISTINCT TEST.* FROM ( SELECT rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService FROM sbi_l_radioservicecodes rsc INNER JOIN sbi_l_radioservicecodegroups rscg ON rsc.radioservicecodeid = rscg.radioservicecodeid WHERE rscg.radioservicegroupid IN (select val from dbo.fnParseArray(@RadioServiceGroup,',')) OR @RadioServiceGroup IS NULL ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService ) as TEST