Clash Royale CLAN TAG#URR8PPP

How can I specify a specific custom ordering with a SELECT DISTINCT

I've seen all the other answers on this but I haven't seen my specific problem addressed. Basically, I have a table with a bunch of duplicated values, and I need to select exactly one column (to concatenate into a list, ultimately, for output purposes), but with a specific item in that list FIRST. The problem I have is that I can specify the order, or I can specify distinct, but not both. And I CANNOT INCLUDE the order column in the SELECT because that output is going to be used directly, and having two columns in the output breaks everything. This seems like it should be possible, but I can't figure out how.

Here's a contrived example:

DECLARE @List TABLE ([Name] nvarchar(10));
INSERT INTO @List ([Name])
VALUES (N'A'), (N'A'), (N'B'), (N'B'), (N'B'), (N'C'), (N'D'), (N'D'), (N'J'), (N'X'), (N'X'), (N'Y');

-- Has both duplicates, and not in the right order
SELECT * FROM @List;
-- No duplicates, but not in the right order
SELECT DISTINCT * FROM @List;
-- In the right order, but has duplicates
SELECT * FROM @List ORDER BY CASE WHEN [Name] = 'X' THEN '1' WHEN [Name] = 'Y' THEN '2' ELSE [Name] END;


What I want is a simple output like this:

X
Y
A
B
C
D
J


I've tried various approaches, with CTEs, and intermediate steps, but I really just want ONE select, that I can throw into a STUFF, so I get the string "X, Y, A, B, C, D, J", and I can't find any way to preserve the REQUIRED order while doing that. This HAS to be possible, so I'm clearly missing something...

2 Answers
2

You should use a group by instead of distinct.

SELECT [Name]
FROM @List
group by [Name]
ORDER BY CASE WHEN [Name] = 'X' THEN '1' WHEN [Name] = 'Y' THEN '2' ELSE [Name] END ;


Unfortunately I do not have enough points to post a comment, so try to figure it out in an "answer".
You've mentioned that you should not include ordering column in the result set, so I assume that column defined in the table.

If so, then you can use a window functions for that purpose. Assume that column has a name Ordering_Column, then you may issue this:

Ordering_Column

select Name
from (
select Name
, row_number() over (partition by Name order by Ordering_Column) as Position
from SomeTable
order by Ordering_Column
) where Position = 1


This would return firstmost Name values from SomeTable sorted by Ordering_Column.

Name

SomeTable

Ordering_Column

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.