How to Show data from a column separated by Character values first and then numeric values in sql server

Question

Suppose i have a table

id  Values
1   A
2   B
3   C
4   7
5   J
6   K
7   L

Now i want an output like

id  Values
1   A
2   B
3   C
4   J
5   K
6   L
7   7

How this can be achieved ?

Answer 1

Generate ROW_NUMBER() with independent sorting on each column using subquery/CTE and join using generated rn:

SqlFiddleDemo

;WITH cte AS
(
  SELECT
   [id],
   rn = ROW_NUMBER() OVER(ORDER BY id)
  FROM tab
)  
,cte2 AS
(
  SELECT
   [values],
   rn = ROW_NUMBER() OVER(ORDER BY CASE
           WHEN  LEFT([values],1) LIKE '%[A-Z]%'
           THEN 1 
           ELSE 2
         END, [values])
  FROM tab
)  

SELECT c1.id, c2.[values]
FROM cte c1
JOIN cte2 c2
  ON c1.rn = c2.rn;