CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
user2689161
user2689161

Reputation: 13

Dynamic Order By using parent value and mixed column types

I have a table named Report, the columns in Report as followed:

Id(char)     
Name(char)     
ParentId(char)     
Sequence(int)     
SortBy(char)

The table has some hierarchies. Each row's ParentId is another row's Id(the top hierarchy's ParentId is NULL). The SortBy filed is either "Name" or "Sequence".

Now I want get a SELECT * FROM Report. The result I want to get is group by the ParentId, but in each group, it is ordered by SortBy, where the SortBy's value is in the row Id = this group 's ParentId.

More specifically, if a group's ParentId is "animal", the SortBy of the row that Id is "animal" is "Name", I want this group is sorted by "Name".

Is anyone can help? Thanks so much for your time!

Upvotes: 1

Views: 283

Answers (3)

Daniel Gimenez
Daniel Gimenez

Reputation: 20619

You can use a CASE in the order by to dynamically sort by the parent. The only tricky issue with your case is that the columns you can sort by are different data types. In that case you have to cast them to a common data type.

Query

The query is simple. Left join to the parent and use the parent to determine the second sort parameter in the case. The first sort parameter is the parentId -- that keeps the children grouped together.

There are two ways to handle Sequence. You can either cast it as an varachar and use pad, or add a third sort parameter. There was no change in the execution plan, but I expect separating the order by columns by type is better if there is an index for the Sequence column

SELECT Child.*
FROM #Report [Child]
LEFT JOIN #Report [Parent] ON Parent.Id = Child.ParentId
ORDER BY 
    Child.ParentId,
    CASE Parent.SortBy WHEN 'Name' THEN Child.Name END,
    Sequence -- sort by sequence if no column is matched

Alternative ORDER BY

ORDER BY 
    Child.ParentId,
    CASE Parent.SortBy
        WHEN 'Name' THEN Child.Name
        ELSE RIGHT('0000000000' + CAST(Child.Sequence AS VARCHAR), 10)
    END

Setup Code

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE NAME LIKE '#Report%') 
    DROP TABLE #Report;

CREATE TABLE #Report
(
    Id CHAR(20),     
    Name CHAR(20),
    ParentId CHAR(20),
    Sequence INT,
    SortBy CHAR(20)
);

INSERT INTO #Report VALUES
('a', 'zName', 'f', 2, ''),
('b', 'bName', 'f', 3, ''),
('c', 'cName', 'g', 7, ''),
('d', 'dName', 'g', 5, ''),
('e', 'eName', 'g', 6, ''),
('f', 'fName', '', 9, 'Name'),
('g', 'gName', '', 8, 'Sequence');

Output

Id  Name    ParentId    Sequence    SortBy
f   fName               9           Name                
g   gName               8           Sequence            
b   bName    f          3                       
a   zName    f          2                       
d   dName    g          5                       
e   eName    g          6                       
c   cName    g          17

Upvotes: 2

Luis LL
Luis LL

Reputation: 2993

This is an alternative option using RANK OVER and UNION 

SELECT Child.*, RANK() OVER (PARTITION BY Child.ParentID ORDER BY Child.Sequence) AS Ranked
FROM Report Child
LEFT JOIN Report Parent  ON Parent.ID = Child.ParentID
WHERE Parent.SortBy = 'Sequence'
ORDER BY Child.ParentID, Ranked
SELECT Child.*, RANK() OVER (PARTITION BY Child.ParentID ORDER BY Child.Name) AS Ranked
FROM Report Child
LEFT JOIN Report Parent  ON Parent.ID = Child.ParentID
WHERE Parent.SortBy = 'Name'
ORDER BY Child.ParentID, Ranked

Upvotes: 1

i-one
i-one

Reputation: 5120

select r.*
from Report r
    left join Report p on p.ID = r.ParentID
order by r.ParentID,
    case p.SortBy
        when 'Sequence' then right('0000000000' + cast(r.Sequence as varchar(10)), 10)
        else r.Name
    end

Upvotes: 2

Related Questions