Sean Lange (3/1/2013)
Lowell, you would have to add a where clause to only return those rows that match. Notice below this will return a cartesian product.
select *
from @test1 t
OUTER apply dbo.delimitedsplit8k('d;c;b;a;e',';') myfn
ORDER BY CASE WHEN LEFT(letter,1) = myfn.Item THEN 1 ELSE 2 END,myfn.ItemNumber
The top 5 rows are sorted perfectly but it returned 25 rows instead of 5.
Add the where clause and it works correctly.
select *
from @test1 t
OUTER apply dbo.delimitedsplit8k('d;c;b;a;e',';') myfn
where t.letter = myfn.Item
ORDER BY CASE WHEN LEFT(letter,1) = myfn.Item THEN 1 ELSE 2 END,myfn.ItemNumber
Dear Sean,
The proposed command works well. However, if we have repeated value of same letter like :
DECLARE @test1 TABLE (letter varchar(1))
INSERT INTO @test1(letter) VALUES('a')
INSERT INTO @test1(letter) VALUES('b')
INSERT INTO @test1(letter) VALUES('c')
INSERT INTO @test1(letter) VALUES('d')
INSERT INTO @test1(letter) VALUES('a')
INSERT INTO @test1(letter) VALUES('a')
INSERT INTO @test1(letter) VALUES('d')
INSERT INTO @test1(letter) VALUES('d')
running the command
select letter
from @test1 t
OUTER apply dbo.delimitedsplit8k('d;c;b;a;e',';') myfn
where t.letter = myfn.Item
ORDER BY CASE WHEN LEFT(letter,1) = myfn.Item THEN 1 ELSE 2 END,myfn.ItemNumber
will return all rows. I tried to add SELECT DISTINCT to above command :
Select Distinct Letter from
(select letter
from @test1 t
OUTER apply dbo.delimitedsplit8k('d;c;b;a;e',';') myfn
where t.letter = myfn.Item
ORDER BY CASE WHEN LEFT(letter,1) = myfn.Item THEN 1 ELSE 2 END,myfn.ItemNumber) xx
But it raise the error :
Msg 1033, Level 15, State 1, Line 16
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
How could I manage to avoid repeated values ?
Thanks