CODEWITHSUNDEEP
sqlsql-server
ylleigh12
ylleigh12

Reputation: 147

How to sort a field multiple times?

I have a SQL Server table [hobbies] which looks like this:

id        hobby        skills          organization
-------------------------------------------------------
1         reading      encoding        greenpeace
2         cooking      web design      red cross
3         baking       programming     convoy of hope

And I want to have a query with a result like this:

 hobby        skills          organization
------------------------------------------------
 reading      programming     greenpeace
 cooking      web design      red cross
 baking       encoding        convoy of hope

hobby is sorted by id in ascending, skills is sorted also by id in descending and lastly organization is sorted by id in ascending.

Is it possible to do that?

Upvotes: 1

Views: 451

Answers (3)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

It is a very strange organisation of data, but if you insist, it can be done, for example, using ROW_NUMBER function.

Sample data - I've added employeenumber that you mentioned in the comments.

DECLARE @T TABLE (
id int, 
hobby nvarchar(255), 
skills nvarchar(255), 
organization nvarchar(255), 
employeenumber int);

INSERT INTO @T VALUES
(11, 'reading', 'encoding   ', 'greenpeace    ', 123),
(12, 'cooking', 'web design ', 'red cross     ', 123),
(13, 'baking ', 'programming', 'convoy of hope', 123),
(21, 'reading', 'encoding   ', 'greenpeace    ', 222),
(22, 'cooking', 'web design ', 'red cross     ', 222),
(23, 'baking ', 'programming', 'convoy of hope', 222);

Query

WITH
CTE
AS
(
    SELECT
        id
        ,hobby
        ,skills
        ,organization
        ,ROW_NUMBER() OVER (ORDER BY id ASC) AS rn_asc
        ,ROW_NUMBER() OVER (ORDER BY id DESC) AS rn_desc
    FROM @T AS hobbies
    WHERE employeenumber = 222
)
SELECT
    C1.id
    ,C1.hobby
    ,C2.skills
    ,C1.organization
FROM
    CTE AS C1
    INNER JOIN CTE AS C2 ON C2.rn_desc = C1.rn_asc
ORDER BY C1.rn_asc;

Result

+----+---------+-------------+----------------+
| id |  hobby  |   skills    |  organization  |
+----+---------+-------------+----------------+
| 21 | reading | programming | greenpeace     |
| 22 | cooking | web design  | red cross      |
| 23 | baking  | encoding    | convoy of hope |
+----+---------+-------------+----------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

This isn't how tables work, but it is possible to accomplish this. One method is aggregation and union all:

select max(hobby) as hobby, max(skills) as skills,
       max(organization) as organization
from ((select row_number() over (order by id asc) as seqnum, hobby, NULL as skills, organization
       from hobbies
      ) union all
      (select row_number() over (order by id desc), NULL, skills, NULL
       from hobbies
      )
     ) h
group by seqnum
order by seqnum;

Upvotes: 0

CJ Oster
CJ Oster

Reputation: 211

That's not how tables work. Each of your tuples, (id, hobby, skills, organization) constitutes a row. You can change the order of (entire) rows, but not sort the columns independently and on arbitrary conditions.

Upvotes: 0

Related Questions