0

I am getting error while using Norwegian_100_CI_AI with DISTINCT while using Norwegian culture with DISTINCT.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

If I changed the culture to DEFAULT or remove it will run query. Kindly help.

CREATE TABLE Test1
(ID INT,
NAME VARCHAR(200)
)

INSERT Test1 (ID, NAME) 
VALUES (1, 'T1'), (2, 'T2'), (3, 'T3'),(4, 'T4'),(5, 'T5'),(6, 'T6'),(7, 'T7'),(8, 'T8'),(9, 'T9'),(10, 'T10')

SELECT DISTINCT ID, NAME FROM Test1
ORDER BY NAME COLLATE Norwegian_100_CI_AI 


SELECT DISTINCT ID, NAME FROM Test1
ORDER BY NAME COLLATE Norwegian_100_CI_AI
Thom A
  • 88,727
  • 11
  • 45
  • 75
user3083835
  • 33
  • 1
  • 1
    `COLLATE` the value in your `SELECT` as well. `NAME` and `NAME COLLATE Norwegian_100_CI_AI ` are *not* the same item, they are different collations and hence the error telling you so. – Thom A Dec 14 '21 at 09:11
  • @Larnu Oddly enough this restriction isn't actually necessary in this case. It's normally because `DISTINCT` functions like `GROUP BY`, so you don't have the original results anymore, so it would make sense if the `COLLATE` was in the `SELECT` but *not* in the `ORDER BY`. But here `NAME` is actually in the `SELECT`, so it should have worked. Compare with a `GROUP BY` where it works https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f1e4b99b8efd31db545ac3443c658d8e – Charlieface Dec 14 '21 at 11:03
  • 1
    I *really* dislike using `GROUP BY` as a replacement for `DISTINCT` @Charlieface. There's no aggregation in your query; it makes little sense to have a `GROUP BY`. – Thom A Dec 14 '21 at 11:25
  • @Larnu Matter of taste I suppose. I generally avoid `DISTINCT` because it operates over all columns (eg if you have a concatenated field it will group by that, rather than grouping by the components). `GROUP BY` is much clearer in regards what is actually being grouped, why does it have to have an aggregation to do that? `DISTINCT` also feels like a code-smell that someone threw at a query to get rid of dupes without thinking through the joins. – Charlieface Dec 14 '21 at 11:51
  • "Why does it have to have an aggregation to do that?" It's not that it is required, but most of the time a group by is used, it is because there was an aggregate used. It's just slightly jarring to use group by in that instance, since the goal is to get distinct/unique values, not group on an aggregate. Using group by in this way would not be as obvious to future developers as a distinct would, which I think should be the most important aspect to consider (as long as performance was not affected) – J. Rockwood Jan 31 '22 at 19:17

0 Answers0