• Register
0 votes
252 views

Problem :

I know that my query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT DISTINCT mycity
FROM Employees
WHERE mycountry = N'USA' AND myregion = N'WA'
ORDER BY mybirthdate;

What is a reason for this ? What is actually happening ? If I don't use the DISTINCT or just add the birthdate in SELECT or ORDER BY city only, it gives me some output, but not the error. Is it because SELECT DISTINCT mycity only gives the result set with cities and nothing else ?

7 5 2
3,870 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

A query with the SELECT DISTINCT can be rewritten using GROUP BY. So the query as below:

SELECT DISTINCT mycity
FROM Employees
WHERE mycountry = N'USA' AND myregion = N'WA' ;

Is equivalent to the below one:

SELECT mycity
FROM Employees
WHERE mycountry = N'USA' AND myregion = N'WA'
GROUP BY mycity ;

And you can not use the ORDER BY mybirthdate here either. The reason is that the same for both queries. There may be many (more than one) rows with the same mycity but different mybirthdate.

You can however use the aggregate functions with the GROUP BY query.

Hope this clears your doubts.

9 7 4
38,600 points
0 votes

Solution:

Try this:

ORDER BY 1, 2

OR

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

The real issue is caused by data type conversion. If you add this expression to the select list:

CASE WHEN c.ID > 10 THEN c.Title
        ELSE c.ID
        END

you will see another error. Something like: "Conversion failed when converting the nvarchar value 'Some title' to data type int". According to data type precedence when you compare varchar with int values (SQL Server will need to compare Title with ID to sort your final result set) varchar should be converted to int because int has higher precedence.

While they are not the same thing, in one sense DISTINCT implies a GROUP BY, because every DISTINCT could be re-written using GROUP BY instead. With that in mind, it doesn't make sense to order by something that's not in the aggregate group.

For example, if you have a table like this:

col1  col2
----  ----
 1     1
 1     2
 2     1
 2     2
 2     3
 3     1

then try to query it like this:

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

That would make no sense, because there could end up being multiple col2 values per row. Which one should it use for the order? Of course, in this query you know the results wouldn't be that way, but the database server can't know that in advance.

Now, your case is a little different. You included all the columns from the order by clause in the select clause, and therefore it would seem at first glance that they were all grouped. However, some of those columns were included in a calculated field. When you do that in combination with distinct, the distinct directive can only be applied to the final results of the calculation: it doesn't know anything about the source of the calculation any more.

This means the server doesn't really know it can count on those columns any more. It knows that they were used, but it doesn't know if the calculation operation might 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 are okay to use for ordering. There are several ways to do that, but this approach should work okay:

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

When you define concatenation you need to use an ALIAS for the new column if you want to order on it combined 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

 

10 6 4
31,120 points

Related questions

0 votes
1 answer 2 views
2 views
Problem: I am unable to find out the solution, Please solve it : the order of the columns returned by an sql select statement is determined by the:
asked 1 day ago rakib1 51.5k points
0 votes
1 answer 4 views
4 views
Problem: I'll make a submission for a response to my question. Please, I have searched the internet but have not found any useful material, and I am now having trouble continuing my studies.
asked Mar 30 tuhin1 48.4k points
0 votes
1 answer 5 views
5 views
Problem: I was always of the impression that a query with no specified 'Order by' rule, would order this by the results by what was specified within your where clause.
asked Mar 14 ummesalma 24.5k points
0 votes
2 answers 108 views
108 views
Problem: The six clauses of the SELECT statement must be coded in the following order:?
asked Apr 18, 2020 ArifulIslam 7.5k points
0 votes
1 answer 4 views
4 views
Problem: Hi I need the solution of this problem please;; column must appear in the group by clause
asked Mar 20 PkGuy 13.1k points
0 votes
1 answer 151 views
151 views
Problem : I want to select a maximum avg for each cname in my table. SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname; But I will get the below error, ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate ... )  FROM makerar GROUP BY cname; How can I go about fixing above error? Note: This table is the VIEW created from the previous operation.
asked Jan 22, 2020 jwilliam 3.9k points
0 votes
1 answer 4 views
4 views
Problem: How to select a partial of a table for exporting using SQL? How can I do that? Thanks
asked Mar 9 ummesalma 24.5k points
0 votes
1 answer 1 view
1 view
Problem: please help to fiond solution... what is the correct order of clauses in a select statement?
asked Apr 1 Ifra 24.4k points
0 votes
1 answer 161 views
161 views
Problem : I am facing following error with the below query in SQL Server 2012. An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'. SQL Query: CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo ... JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo WHERE t.TaskManagerNo = @UserResourceNo OR t.AlternateTaskManagerNo = @UserResourceNo ) RETURN END
asked Dec 4, 2019 alecxe 7.5k points
0 votes
1 answer 1 view
1 view
Problem: Is there someone who can assist me in resolving this issue? Which of the following input fields allow the user to select multiple values
asked Mar 30 tuhin1 48.4k points