Post reply

ORDER By Based on Company then SubCompany

  • December 7, 2017 at 1:15 pm

    #330414

    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

  • December 7, 2017 at 1:22 pm

    #1971271

    Whata have you tried so far?

    Thom~

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

  • December 7, 2017 at 1:36 pm

    #1971272

    Add a suitable ORDER BY clause to your SQL statement.

  • December 7, 2017 at 3:36 pm

    #1971289

    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!

  • 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

  • December 8, 2017 at 9:12 am

    #1971395

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 8, 2017 at 10:02 am

    #1971403

    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!

  • December 8, 2017 at 1:00 pm

    #1971425

    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

  • December 8, 2017 at 2:52 pm

    #1971434

    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)

  • December 9, 2017 at 12:35 pm

    #1971456

    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. 

  • December 9, 2017 at 10:13 pm

    #1971465

    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)

  • December 28, 2017 at 3:06 pm

    #1973777

    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

  • December 28, 2017 at 3:09 pm

    #1973778

    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/

  • December 28, 2017 at 3:25 pm

    #1973782

    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. 

  • December 28, 2017 at 4:18 pm

    #1973789

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy