Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL ORDER By Based on Company then SubCompany RE: ORDER By Based on Company then SubCompany

  • December 8, 2017 at 8:26 am

    #1971384

    Jacob Wilkins - Thursday, December 7, 2017 3:36 PM

    In the future, posting the sample data as a create table script with inserts for the sample data would help us out quite a bit (otherwise to test solutions we need to manually get all that data entered somewhere).

    Here's a good link on how to do that: https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    Anyway, perhaps something like this is what you're looking for?

    IF OBJECT_ID('tempdb.dbo.#companies') IS NOT NULL DROP TABLE #companies;

    CREATE TABLE #companies
    (
    CompanyName varchar(20),
    CompanyStatus varchar(20),
    CompanyNumber varchar(10),
    GroupNumber varchar(10)
    );

    INSERT INTO #companies
    VALUES
    ('Blackwell', 'group','A123G','A123'),
    ('Symphony','subCompany','s678','A123'),
    ('Blends','subCompany', 't123','A123'),
    ('Tiger', 'group','T123G','T123'),
    ('Pepper', 'group','Z987G','Z987'),
    ('Great Falls','subCompany','f545','T123');

    WITH  ordered_parent_companies AS
    (
    SELECT GroupNumber, rn=ROW_NUMBER() OVER (ORDER BY CompanyName)
    FROM  #companies
    WHERE  CompanyStatus='group'
    )

    SELECT c.*
    FROM  #companies c
       INNER JOIN
       ordered_parent_companies opc ON c.GroupNumber=opc.GroupNumber
    ORDER BY rn ASC,
       CASE WHEN CompanyStatus='group' THEN 0 ELSE 1 END ASC,
       --The second sort could simply be on CompanyStatus if only 'group' and 'subCompany' are possible values
       --but I wanted to make sure if there were other possible values for CompanyStatus that the 'group'
       --company sorted first
       CompanyName ASC;

    Cheers!

    I think that you've over-complicated things here.  There is no indication that there can be more than one group name, so the ROW_NUMBER() isn't needed especially since it adds an unnecessary sort.  Here is a simple self-join that accomplishes the same thing.
    SELECT c.*
    FROM #companies c
    INNER JOIN #companies g
        ON c.GroupNumber = g.GroupNumber
            AND g.CompanyStatus = 'group'
    ORDER BY g.CompanyName, c.CompanyName
    ;

    That being said, there is another method that may perform better, because it only requires a single scan of the table (although that may be offset by an additional sort).

    ;
    WITH Company_Groups AS
    (
        SELECT c.*, FIRST_VALUE(c.CompanyName) OVER(PARTITION BY c.GroupNumber ORDER BY c.CompanyStatus ROWS UNBOUNDED PRECEDING) AS GroupName
        FROM #companies c
    )
    SELECT CompanyName, CompanyStatus, CompanyNumber, GroupNumber
    FROM Company_Groups
    ORDER BY GroupName, CompanyName
    ;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA