Need help with sorting and/or grouping with maybe partition

Transact-SQL
1

Hi !

I'm sorry if it has already been asked, I can't put the right keyword to search on how to acocmplish this.

I can't figure out how to accomplish the following : "Sort all the results by StartDate, as soon as out hit the first date, takes the EmployeeFullName and put every other record sorted by StartDate ascending"

The following table

EmployeefullName StartDate Subject

Emp1 2017-01-01 Appointment of 2017-01-01
Emp2 2017-01-03 Appointment of 2017-01-03
Emp3 2017-01-04 Appointment of 2017-01-04
Emp2 2017-01-04 Appointment of 2017-01-04
Emp2 2017-01-05 Appointment of 2017-01-05
Emp3 2017-01-06 Appointment of 2017-01-06
Emp3 2017-01-07 Appointment of 2017-01-07
Emp3 2017-01-09 Appointment of 2017-01-09
Emp1 2017-01-10 Appointment of 2017-01-10
Emp1 2017-01-15 Appointment of 2017-01-15

Should be output as (add LF/CR for clarity)

EmployeefullName StartDate Subject

Emp1 2017-01-01 Appointment of 2017-01-01
Emp1 2017-01-10 Appointment of 2017-01-10
Emp1 2017-01-15 Appointment of 2017-01-15

Emp2 2017-01-03 Appointment of 2017-01-03
Emp2 2017-01-04 Appointment of 2017-01-04
Emp2 2017-01-05 Appointment of 2017-01-05

Emp3 2017-01-04 Appointment of 2017-01-04
Emp3 2017-01-06 Appointment of 2017-01-06
Emp3 2017-01-07 Appointment of 2017-01-07
Emp3 2017-01-09 Appointment of 2017-01-09

2 
WITH tbl (EmployeefullName, StartDate, Subject, RowNum) AS
(
SELECT e, d, s, Row_Number() OVER (PARTITION BY e ORDER BY d)
FROM (
     VALUES
       ('Emp1', Cast('20170101' AS date), 'Appointment of 2017-01-01')
     , ('Emp2', Cast('20170103' AS date), 'Appointment of 2017-01-03')
     , ('Emp3', Cast('20170104' AS date), 'Appointment of 2017-01-04')
     , ('Emp2', Cast('20170104' AS date), 'Appointment of 2017-01-04')
     , ('Emp2', Cast('20170105' AS date), 'Appointment of 2017-01-05')
     , ('Emp3', Cast('20170106' AS date), 'Appointment of 2017-01-06')
     , ('Emp3', Cast('20170107' AS date), 'Appointment of 2017-01-07')
     , ('Emp3', Cast('20170109' AS date), 'Appointment of 2017-01-09')
     , ('Emp1', Cast('20170110' AS date), 'Appointment of 2017-01-10')
     , ('Emp1', Cast('20170115' AS date), 'Appointment of 2017-01-15')
     ) t(e, d, s)
     )
SELECT
    tbl.EmployeefullName
  , tbl.StartDate
  , tbl.Subject
FROM tbl
ORDER BY tbl.EmployeefullName, tbl.RowNum ;
EmployeefullName StartDate  Subject
---------------- ---------- -------------------------
Emp1             2017-01-01 Appointment of 2017-01-01
Emp1             2017-01-10 Appointment of 2017-01-10
Emp1             2017-01-15 Appointment of 2017-01-15
Emp2             2017-01-03 Appointment of 2017-01-03
Emp2             2017-01-04 Appointment of 2017-01-04
Emp2             2017-01-05 Appointment of 2017-01-05
Emp3             2017-01-04 Appointment of 2017-01-04
Emp3             2017-01-06 Appointment of 2017-01-06
Emp3             2017-01-07 Appointment of 2017-01-07
Emp3             2017-01-09 Appointment of 2017-01-09

(10 row(s) affected)

3

This is exactly what I was looking for.

How do you name this kind of grouping ?

Thank you very much !

4

I might be missing something here but, between the given definition and the desired output, I see no reason for the complexity of adding ROW_NUMBER().

 SELECT *
   FROM (--===== This simulates the real table
         VALUES
             ('Emp1', Cast('20170101' AS date), 'Appointment of 2017-01-01')
            ,('Emp2', Cast('20170103' AS date), 'Appointment of 2017-01-03')
            ,('Emp3', Cast('20170104' AS date), 'Appointment of 2017-01-04')
            ,('Emp2', Cast('20170104' AS date), 'Appointment of 2017-01-04')
            ,('Emp2', Cast('20170105' AS date), 'Appointment of 2017-01-05')
            ,('Emp3', Cast('20170106' AS date), 'Appointment of 2017-01-06')
            ,('Emp3', Cast('20170107' AS date), 'Appointment of 2017-01-07')
            ,('Emp3', Cast('20170109' AS date), 'Appointment of 2017-01-09')
            ,('Emp1', Cast('20170110' AS date), 'Appointment of 2017-01-10')
            ,('Emp1', Cast('20170115' AS date), 'Appointment of 2017-01-15')
        ) d (EmployeefullName, StartDate, Subject)
  ORDER BY EmployeefullName, StartDate
;

Results... same as the previous code.

EmployeefullName StartDate  Subject
---------------- ---------- -------------------------
Emp1             2017-01-01 Appointment of 2017-01-01
Emp1             2017-01-10 Appointment of 2017-01-10
Emp1             2017-01-15 Appointment of 2017-01-15
Emp2             2017-01-03 Appointment of 2017-01-03
Emp2             2017-01-04 Appointment of 2017-01-04
Emp2             2017-01-05 Appointment of 2017-01-05
Emp3             2017-01-04 Appointment of 2017-01-04
Emp3             2017-01-06 Appointment of 2017-01-06
Emp3             2017-01-07 Appointment of 2017-01-07
Emp3             2017-01-09 Appointment of 2017-01-09