Yeo Meng Tat Yeo Meng Tat - 5 months ago 16
SQL Question

SQL Server Custom sorting in ORDER BY clause

ALTER PROCEDURE sp_1
AS
BEGIN
SELECT B.ID, 'B' AS Name
FROM tableB inner join table a inner join table c
UNION
SELECT A.ID, 'A' AS NAME
FROM tableA inner join table b inner join table c
UNION
SELECT C.ID, 'C' AS NAME
FROM tableC inner join table a inner join table b
ORDER BY CASE WHEN Name = 'A' THEN '1'
ELSE Name END ASC
END


The code above returns invalid column name 'Name'. Is there anyway where i can sort 'name customly by using order by without delcaring any new paramters.

Answer
SELECT B.ID, 'B' AS Name, 1 as ExplicitOrderGroup
FROM tableB inner join table a inner join table c
UNION
SELECT A.ID, 'A' AS NAME, 0 as ExplicitOrderGroup
FROM tableA inner join table b inner join table c
UNION
SELECT C.ID, 'C' AS NAME, 1 as ExplicitOrderGroup
FROM tableC inner join table a inner join table b
ORDER BY ExplicitOrderGroup, Name

Note, UNION (which behaves a bit like DISTINCT) will not collaps now same rows with different ExplicitOrderGroup. You have to determine whethere it's OK or you need some other behaviour.

Comments