CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2sql-server-2012
user4001270
user4001270

Reputation: 19

How to sort a column based on length of data in it in SQL server

As we all know general sorting is using order by. The sort I want to perform is different. I want the smallest length value in middle of table n the largest ones in top and bottom of it. One half should be descending and another half should be ascending. Can you guys help. It was an interview question.

Upvotes: 1

Views: 762

Answers (3)

Lmu92
Lmu92

Reputation: 952

Here's a short approach that would ge t you started:

WITH cte AS
(
    SELECT TOP 1000 number
    FROM master..spt_values 
    WHERE type = 'P' and number >0
)
SELECT number,  row_number() OVER(ORDER BY CASE WHEN number %2 = 1 THEN number ELSE -(number) END) pos 
FROM cte

Upvotes: 0

Lamak
Lamak

Reputation: 70668

This is one way:

;WITH CTE AS
(
    SELECT  *, 
            RN = ROW_NUMBER() OVER(ORDER BY LEN(YourColumn))
    FROM dbo.YourTable
)
SELECT *
FROM CTE
ORDER BY RN%2, (CASE WHEN RN%2 = 0 THEN 1 ELSE -1 END)*RN DESC

Upvotes: 9

M.Ali
M.Ali

Reputation: 69564

Test Data

DECLARE @Table TABLE
(ID INT, Value VARCHAR(10))

INSERT INTO @Table VALUES 
(1 , 'A'),
(2 , 'AB'),
(3 , 'ABC'),
(4 , 'ABCD'),
(5 , 'ABCDE'),
(6 , 'ABCDEF'),
(7 , 'ABCDEFG'),
(8 , 'ABCDEFGI'),
(9 , 'ABCDEFGIJ'),
(10 ,'ABCDEFGIJK')

Query

;WITH CTE AS (
SELECT * 
      ,NTILE(2) OVER (ORDER BY LEN(Value) DESC) rn
FROM @Table )
SELECT *
FROM CTE 
ORDER BY CASE WHEN rn = 1 THEN LEN(Value) END DESC
         ,CASE WHEN rn = 2 THEN LEN(Value) END ASC

Result

╔════╦════════════╦════╗
║ ID ║   Value    ║ rn ║
╠════╬════════════╬════╣
║ 10 ║ ABCDEFGIJK ║  1 ║
║  9 ║ ABCDEFGIJ  ║  1 ║
║  8 ║ ABCDEFGI   ║  1 ║
║  7 ║ ABCDEFG    ║  1 ║
║  6 ║ ABCDEF     ║  1 ║
║  1 ║ A          ║  2 ║
║  2 ║ AB         ║  2 ║
║  3 ║ ABC        ║  2 ║
║  4 ║ ABCD       ║  2 ║
║  5 ║ ABCDE      ║  2 ║
╚════╩════════════╩════╝

Upvotes: 2

Related Questions