Sorting Master Child

Question

Following is the table:

    Groups      Method  RDate
1   master_6    Sales   2019-10-17
2   master_3    ITO     2017-12-22
3   child_6     SRT     2019-10-21
4   master_4    TO      2019-02-07
5   child_3     ITI     2019-03-09
6   child_6     SRT     2019-03-14
7   master_6    Sales   2019-03-14
8   child_4     TR      2019-03-14
9   master_6    Sales   2019-03-14

I want output as follow.

    Groups      Method  RDate
2   master_3    ITO     2017-12-22
5   child_3     ITI     2019-03-09
4   master_4    TO      2019-02-07
8   child_4     TR      2019-03-14
7   master_6    Sales   2019-03-14
6   child_6     SRT     2019-03-14
9   master_6    Sales   2019-03-14
3   child_6     SRT     2019-10-21
1   master_6    Sales   2019-10-17

Logic is:

Take all rows containing word 'master' and sort them by date.

In result, first row shall be Master having the oldest date,

Next row shall be child of that master (master_1's child is Child_1, master_2 child_2, so on)

Then take next master (2nd lowest date), and then its child

for e.g in my case master having the lowest date is rec#2, so that will come first row in result. Then for second row, find child of that master_3, so it will be child_3, (if more than 1 record for child_3 found then consider lowest date and put it at row 2 in result), and then next master record and so on.

hope I did well to explain everything.

drop table if exists #A

CREATE TABLE #A(Groups varchar(15), Method varchar(15), RDate date)
insert into #A values

('master_6','Sales','2019/10/17'),
('master_3','ITO','2017/12/22'),
('child_6','SRT','2019/10/21'),
('master_4','TO','2019/02/07'),
('child_3','ITI','2019/03/09'),
('child_6','SRT','2019/03/14'),
('master_6','Sales','2019/03/14'),
('child_4','TR','2019/03/14'),
('master_6','Sales','2019/03/14');

since there are some confusion, i am trying to explain in other words:

in my case any master(Parent) will have only 1 type of child but can have multiple records of same child, just to explain, let's say that ParentA visited Theater 6 times in a year, and their ChildA visited 5 times. ParentB visited 3 times And their child 3 times. All these records are stored in one table by date, but not in any order.

I want output where it takes all parents and their date in Asc order in the background, then take the first parent from that background list, find it's child's visit- if found many visits then just take child's first visit because his parent visit was also first.

then take second record from parent list and find their child's visit If this is second visit of same parent then find second visit of child, if not found anything then go to the third row of the parent list and find it's child visit.

All remaining(extra) visits of Parent or child will be at the end list.

Answer 1

When a master gives their number to a child, they can stay together.

with CTE_MASTERS as
(
  select Groups, Method, Rdate
  , substring(Groups,patindex('%[_]%',Groups)+1,len(Groups)) as groupNr
  , row_number() over (partition by Groups order by rdate) as groupRownum
  , row_number() over (order by rdate, Groups) as masterRownum
  from #A
  where Groups like 'master%'
)
, CTE_CHILDS as
(
  select Groups, Method, Rdate
  , substring(Groups,patindex('%[_]%',Groups)+1,len(Groups)) as groupNr
  , row_number() over (partition by Groups order by rdate) as groupRownum
  from #A
  where Groups like 'child%'
)
, CTE_MASTERS_AND_CHILDS as
(
  select *
  , cast(1 as bit) as isMaster
  from CTE_MASTERS

  UNION ALL

  select c.*
  , m.masterRownum
  , 0
  from CTE_CHILDS c
  left join CTE_MASTERS m
    on c.groupNr = m.groupNr 
  and c.groupRownum = m.groupRownum
)
SELECT Groups, Method, Rdate
FROM CTE_MASTERS_AND_CHILDS
ORDER BY 
masterRownum,
isMaster desc,
groupNr,
groupRownum;

Groups   | Method | Rdate              
:------- | :----- | :------------------
master_3 | ITO    | 22/12/2017 00:00:00
child_3  | ITI    | 09/03/2019 00:00:00
master_4 | TO     | 07/02/2019 00:00:00
child_4  | TR     | 14/03/2019 00:00:00
master_6 | Sales  | 14/03/2019 00:00:00
child_6  | SRT    | 14/03/2019 00:00:00
master_6 | Sales  | 14/03/2019 00:00:00
child_6  | SRT    | 21/10/2019 00:00:00
master_6 | Sales  | 17/10/2019 00:00:00

db<>fiddle here

Answer 2

For your sample data, you can do this in the order by:

order by max(case when groups like 'master%' then date end) over (partition by right(groups, 1)) asc,
         right(groups, 1),
         (case when groups like 'master%' then 1 else 2 end)

This is doing:

• Calculating the date for the entire group, based on the "master" date for the group.
• Keeping all of a group together, in case there are ties.
• Putting the master record before the child(ren).