Ordinare risultati con spazi vuoti alla fine

>row number semplicemente mi assegna un valore numerico crescente

USE tempdb;
GO

CREATE TABLE #Foo
(
    IDFoo int NOT NULL PRIMARY KEY CLUSTERED
  , FooData char(1) NOT NULL
)
GO

INSERT INTO #Foo (IDFoo, FooData)
VALUES (1, '' );
INSERT INTO #Foo (IDFoo, FooData)
VALUES (2, '' );
INSERT INTO #Foo (IDFoo, FooData)
VALUES (3, 'D' );
INSERT INTO #Foo (IDFoo, FooData)
VALUES (4, 'A' );
INSERT INTO #Foo (IDFoo, FooData)
VALUES (5, 'C' );
GO


-- order by normale
SELECT 
    IDFoo
  , FooData 
FROM 
  #Foo 
ORDER BY
  FooData
GO

-- order by case
SELECT 
    IDFoo
  , FooData 
FROM 
  #Foo 
ORDER BY
  CASE 
    WHEN LTRIM(RTRIM(FooData)) = '' 
    THEN 'Z' -- max value (per farlo finire in fondo)
    ELSE FooData 
  END
GO


DROP TABLE #Foo;
GO