x

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

more ▼

asked Jul 07 at 07:49 AM in Default

avatar image

Drahc
41 1 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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)
more ▼

answered Jul 07 at 07:58 AM

avatar image

Kev Riley ♦♦
65.9k 48 63 81

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.

Jul 07 at 07:59 PM Drahc

The temp table was just there to demonstrate the query, no need to use it in the real code

Jul 10 at 09:00 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jul 07 at 11:19 AM

avatar image

Mart
529 1 6 10

(comments are locked)
10|1200 characters needed characters left

with cte as ( select distinct test1,test2 from tbltest)

select test1 from cte ORDER BY test2

more ▼

answered Jul 12 at 01:23 PM

avatar image

Waqar_lionheart
101 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28
x22
x9
x3

asked: Jul 07 at 07:49 AM

Seen: 109 times

Last Updated: Jul 12 at 01:23 PM

Copyright 2017 Redgate Software. Privacy Policy