CODEWITHSUNDEEP
sqlsql-servert-sql
Depth of Field
Depth of Field

Reputation: 317

Sorting DISTINCT values without being in SELECT statement

I'm using SQL Server 2014, and working with the following table which has repeating values:

COLUMN_NAME COLUMN_POS
SAL         3
SAL         3
EMP         1
CITY        2
NAME        4
JOB_TITLE   5
JOB_TITLE   5
EMP         1
CITY        2
NAME        4

I need to output a distinct list of the COLUMN_NAME values, sorted by the COLUMN_POS without the COLUMN_POS being included in the query results.

I'm using this later on in the query for the purposes of Pivoting columns dynamically. Desired output:

COLUMN_NAME 
EMP         
CITY        
SAL         
NAME        
JOB_TITLE

I've tried:

(1)

SELECT DISTINCT COLUMN_NAME FROM #TEMP  ORDER BY COLUMN_POS ;

(2)

SELECT  COLUMN_NAME FROM #TEMP  GROUP BY COLUMN_POS ORDER BY COLUMN_POS ;

(3)

WITH CTE AS (SELECT  COLUMN_NAME FROM #TEMP  GROUP BY COLUMN_POS ORDER BY COLUMN_POS ) 
 SELECT  DISTINCT COLUMN_NAME FROM #TEMP

Which all return errors, indicating COLUMN_POS is not in the SELECT list, or The ORDER BY clause is invalid in views,

Sample values:

CREATE TABLE #temp (
COLUMN_NAME NVARCHAR(MAX)
,COLUMN_POS int 
) 

INSERT INTO #TEMP
(COLUMN_NAME,COLUMN_POS) 
values 

('SAL',3 ),
('EMP', 1),
('CITY', 2),
('NAME', 4),
('JOB_TITLE', 5),
('CITY', 2),
('NAME', 4),
('NAME', 4)

Upvotes: 0

Views: 44

Answers (1)

Dale K
Dale K

Reputation: 27472

Assuming you have the same position for every distinct name (which your sample data indicates) then whats wrong with:

SELECT COLUMN_NAME
FROM #Temp
GROUP BY COLUMN_NAME, COLUMN_POS
ORDER BY COLUMN_POS;

Upvotes: 2

Related Questions