question

Drahc avatar image
Drahc asked

Select DISTINCT from one column but ORDER BY another

I have a table with repeating Name column already in a specific order that I need to pull out only the DISTINCT values from Name column but they must stay in the order they are in the table. I thought I could do this by creating another column and call it Order, but I do not want the Order column to return at all, only the Name column. Name Order G 1 G 1 G 1 C 2 C 2 P 3 P 3 P 3 M 4 M 4 M 4 M 4 M 4 M 4 M 4 M 4 M 4 S 5 S 5 S 5 S 5 S 5 S 5 I need the above to return only. Name G C P M S and in that order
order-bydistinctorderingorder
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Here's one way. You need to preserve the order column with the aggregation, then you can use it to sort the final output. declare @YourTable table (name char(1), [order] int); insert into @YourTable select 'G', 1 union all select 'G', 1 union all select 'G', 1 union all select 'C', 2 union all select 'C', 2 union all select 'P', 3 union all select 'P', 3 union all select 'P', 3 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 select name from (select distinct name, [order] from @YourTable ) distinctnames order by [order] name ---- G C P M S (5 row(s) affected)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This helped! For some reason just doing select name from (select distinct name, [order] from MyTable ) distinctnames order by [order] Worked without needing @ temp table.
0 Likes 0 ·
The temp table was just there to demonstrate the query, no need to use it in the real code
1 Like 1 ·
Mart avatar image
Mart answered
Here's a slightly different approach that uses aggregate functions instead of nested query - just for fun :) I've used Kev's table build to keep it simple: declare @YourTable table (name char(1), sortorder int); insert into @YourTable select 'G', 1 union all select 'G', 1 union all select 'G', 1 union all select 'C', 2 union all select 'C', 2 union all select 'P', 3 union all select 'P', 3 union all select 'P', 3 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'M', 4 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 union all select 'S', 5 SELECT MIN(Name) AS Name FROM @YourTable GROUP BY SortOrder ORDER BY SortOrder
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Waqar_lionheart avatar image
Waqar_lionheart answered
with cte as ( select distinct test1,test2 from tbltest) select test1 from cte ORDER BY test2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.