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
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)
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.
The temp table was just there to demonstrate the query, no need to use it in the real code
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
with cte as ( select distinct test1,test2 from tbltest) select test1 from cte ORDER BY test2
22 People are following this question.
