ORDER By Based on Company then SubCompany

  • Hello,
    Trying to create a selection in a specific order.
    The table is like this
     
    CompanyName , CompanyStatus, CompanyNumber, GroupNumber
    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
    ~~~~~~~~~~~~~
     
    What I would like the result to be based on Alpha for the group names, then Alpha for their sub companies
     
    CompanyName , CompanyStatus, CompanyNumber, GroupNumber
    Blackwell              group,                   A123G,                 A123
    Blends,                 subCompany,        t123,                    A123
    Symphony            subCompany,        s678,                   A123
    Pepper                  group,                   Z987G,                Z987
    Great Falls,          subCompany,        f545,                   T123
    Tiger                    group,                    T123G,                T123

    Any ideas?
    Thx
    JS

  • Whata have you tried so far?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Add a suitable ORDER BY clause to your SQL statement.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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,
          CompanyName ASC;

    Cheers!

  • 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

  • There's no need to join the group row to itself, so this is another possibility:


    SELECT c.*
    FROM #companies c
    LEFT OUTER JOIN #companies g
      ON c.CompanyStatus <> 'group'
       AND c.GroupNumber = g.GroupNumber
       AND g.CompanyStatus = 'group'
    ORDER BY ISNULL(g.CompanyName, c.CompanyName), c.CompanyName

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Yeah, if we assume there is no possibility of parent companies with the same name then we can make it simpler; without knowing that for sure, though, I figured I'd start with the safe version.

    After taking a second look, I do notice that my brain added a requirement that isn't actually there in the OP and gives the results in the wrong order (I somehow fabricated a requirement that the 'group' company sort first within each group), so the second expression in my ORDER BY isn't needed.

    I've fixed that in my original query.

    Cheers!

  • ScottPletcher - Friday, December 8, 2017 9:12 AM

    There's no need to join the group row to itself, so this is another possibility:


    SELECT c.*
    FROM #companies c
    LEFT OUTER JOIN #companies g
      ON c.CompanyStatus <> 'group'
       AND c.GroupNumber = g.GroupNumber
       AND g.CompanyStatus = 'group'
    ORDER BY ISNULL(g.CompanyName, c.CompanyName), c.CompanyName

    There may not be a need, but it is more efficient, so I'll stick with joining the group row to itself.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Seems to me that the hierarchy here won't change often.  This is a prime candidate for conversion to Nested Sets, which will enable some remarkably efficient queries if the table needs to be queried more than once between additions, deletions, or updates.
    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets 
    It would also and easily allow for multiple levels, if it ever came to that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Friday, December 8, 2017 2:52 PM

    Seems to me that the hierarchy here won't change often.  This is a prime candidate for conversion to Nested Sets, which will enable some remarkably efficient queries if the table needs to be queried more than once between additions, deletions, or updates.
    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets 
    It would also and easily allow for multiple levels, if it ever came to that.

    While I usually advocate nested sets , and using industry standards like DUNS, this might be a case where his homegrown company identifier can hold the hierarchy. Basically a Dewey decimal of companies

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • jcelko212 32090 - Saturday, December 9, 2017 12:35 PM

    Jeff Moden - Friday, December 8, 2017 2:52 PM

    Seems to me that the hierarchy here won't change often.  This is a prime candidate for conversion to Nested Sets, which will enable some remarkably efficient queries if the table needs to be queried more than once between additions, deletions, or updates.
    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets 
    It would also and easily allow for multiple levels, if it ever came to that.

    While I usually advocate nested sets , and using industry standards like DUNS, this might be a case where his homegrown company identifier can hold the hierarchy. Basically a Dewey decimal of companies

    The problem with the homegrown identifier is that it creates a Cartesian Join between the two levels.  It also doesn't alleviate the problem of recalculating that which has not changed and will, indeed, be slow to change.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello All,

    Sorry all for not getting back sooner. Year end and all of a sudden there's a million things to do.  Thank you very much for all the ideas.  .  Also thanks for the tips on how to post the correctly.  Will use them.

    Tried use  SSChampion's answer.  Works pretty well. 90% Correct. The Companies/Groups never have the same name.  And the Sub-Company codes are always different.  There's only about 1,500 records so not worried so much about how much time it takes to pull the data.
    BUT just found out there are a few Companies that are Groups who have Sub-Groups.  And some of those Sub-Groups are Groups of another set of Sub-Groups. 

    Here's a sample of the data.

    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'),
    ('BusinessA','group', 'b123G','b123'),
    ('BusA_SubGroup', 'subcompany','j123','b123'),
    ('BusA_SubGroup_AS_Group', group','A075G','b123'),
    ('BusB_SubGroup_of_BusA_SubGroup_Group','subCompany','A017','A075'),
    ('BusC_SubGroup_of_BusA_SubGroup_Group','subCompany','A180','A075');

    This is what I've been working with.

    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;

    Any suggestions on handling the
    Group with SubGroups That are Groups of SubGroups.
    Right now the data is coming out like this:

    Any ideas are greatly appreciated.
    Thanks
    JS

  • jslist - Thursday, December 28, 2017 3:06 PM

    Hello All,

    Sorry all for not getting back sooner. Year end and all of a sudden there's a million things to do.  Thank you very much for all the ideas.  .  Also thanks for the tips on how to post the correctly.  Will use them.

    Tried use  SSChampion's answer.  Works pretty well. 90% Correct. The Companies/Groups never have the same name.  And the Sub-Company codes are always different.  There's only about 1,500 records so not worried so much about how much time it takes to pull the data.
    BUT just found out there are a few Companies that are Groups who have Sub-Groups.  And some of those Sub-Groups are Groups of another set of Sub-Groups. 

    Here's a sample of the data.

    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'),
    ('BusinessA','group', 'b123G','b123'),
    ('BusA_SubGroup', 'subcompany','j123','b123'),
    ('BusA_SubGroup_AS_Group', group','A075G','b123'),
    ('BusB_SubGroup_of_BusA_SubGroup_Group','subCompany','A017','A075'),
    ('BusC_SubGroup_of_BusA_SubGroup_Group','subCompany','A180','A075');

    This is what I've been working with.

    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;

    Any suggestions on handling the
    Group with SubGroups That are Groups of SubGroups.
    Right now the data is coming out like this:

    Any ideas are greatly appreciated.
    Thanks
    JS

    And what is the expected output supposed to be?  It helps to know what we are trying to accomplish/

  • Jeff Moden - Saturday, December 9, 2017 10:13 PM

    jcelko212 32090 - Saturday, December 9, 2017 12:35 PM

    Jeff Moden - Friday, December 8, 2017 2:52 PM

    The problem with the homegrown identifier is that it creates a Cartesian Join between the two levels.  It also doesn't alleviate the problem of recalculating that which has not changed and will, indeed, be slow to change.

    There is no problem with a hierarchical encoding scheme. We likewould have thing like

    500 Acme Corporation
    510 rocket powered rollerskates Ltd
    520 portable holes division
    521 lids for portable holes
    etc.

    But it means taking the time to build a hierarchy.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Just a guess, but is this what you are looking for?

    CREATE TABLE #companies
    (
    CompanyName varchar(120),
    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'),
    ('BusinessA','group', 'b123G','b123'),
    ('BusA_SubGroup', 'subcompany','j123','b123'),
    ('BusA_SubGroup_AS_Group', 'group','A075G','b123'),
    ('BusB_SubGroup_of_BusA_SubGroup_Group','subCompany','A017','A075'),
    ('BusC_SubGroup_of_BusA_SubGroup_Group','subCompany','A180','A075');

    WITH rCTE AS (
    SELECT
      [c].[CompanyName]
      , [c].[CompanyStatus]
      , [c].[CompanyNumber]
      , [c].[GroupNumber]
      , [SortKey] = CAST([c].[GroupNumber] AS VARCHAR(MAX))
    FROM
      [#companies] AS [c]
    WHERE
      [c].[CompanyNumber] = [c].[GroupNumber] + 'G'
    UNION ALL
    SELECT
      [c].[CompanyName]
      , [c].[CompanyStatus]
      , [c].[CompanyNumber]
      , [c].[GroupNumber]
      , [SortKey] = [r].[SortKey] + CAST([c].[GroupNumber] AS VARCHAR(MAX))
    FROM
      [#companies] AS [c]
      INNER JOIN [rCTE] [r]
        ON [r].[CompanyNumber] = [c].[GroupNumber] + 'G'
           AND [c].[CompanyNumber] NOT LIKE [c].[GroupNumber] + '%'
    )
    SELECT
      [rCTE].[CompanyName]
      , [rCTE].[CompanyStatus]
      , [rCTE].[CompanyNumber]
      , [rCTE].[GroupNumber]
    FROM
      [rCTE]
    ORDER BY
      [rCTE].[SortKey];

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply