• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

TSQL Order by Group 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
0
jshesek
Asked:
jshesek
  • 6
  • 5
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Please try this -

Pepper group, Z987G, Z987 -> This records will come at the last since you need order by on the GroupNumber column...

/*------------------------
SELECT * FROM ORDERS
ORDER BY GroupNumber,CompanyName,CompanyStatus
------------------------*/
CompanyName                     CompanyStatus         CompanyNumber       GroupNumber
------------------------------- --------------------- ------------------- -----------------
Blackwell                       group                 A123G               A123
Blends                          subCompany            t123                A123
Symphony                        subCompany            s678                A123
Great Falls                     subCompany            f545                T123
Tiger                           group                 T123G               T123
Pepper                          group                 Z987G               Z987

(6 row(s) affected)

Open in new window

0
 
Ryan ChongCommented:
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

there is no "group name" in your table/select statement above

assuming there is, you should try:
ORDER BY GroupName, GroupNumber, CompanyName, CompanyStatus

Open in new window


or:

ORDER BY GroupName, GroupNumber, CompanyStatus, CompanyName

Open in new window

0
 
Ryan ChongCommented:
do you need further assistance here?
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
jshesekAuthor Commented:
There is no group name .  Only a company name
0
 
Ryan ChongCommented:
based on your output:

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

I got no idea why Pepper should come before Tiger and Great Falls. can you explain the logic?

the closest I can get is:

CompanyName                                        CompanyStatus                                      CompanyNumber                                      GroupNumber
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Blackwell                                          group                                              A123G                                              A123
Blends                                             subCompany                                         t123                                               A123
Symphony                                           subCompany                                         s678                                               A123
Tiger                                              group                                              T123G                                              T123
Great Falls                                        subCompany                                         f545                                               T123
Pepper                                             group                                              Z987G                                              Z987

Open in new window


select CompanyName , CompanyStatus, CompanyNumber, GroupNumber from OrderTable
ORDER BY GroupNumber,CompanyStatus,CompanyName

Open in new window

0
 
jshesekAuthor Commented:
Sorry for not being clear enough.  
A group is identified by company status of 'group'  or a CompanyNumber ending with a G.
The every entry has a group number that's listed under "GroupNumber"
The result set should be ordered by Group name (in alpha order) - then subCompany name (in alpha order.

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

Open in new window


A new wrinkle has come in.  
Found some of the group that have subCompanies - 1 of the subCompanies is a group to another set of subcompanies.
Data looks like this:
('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');

Open in new window

The result should be looking like this (minus the empty rows):
Example Result Set
It's working with the code below except for the subCompanies that are groups.  
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

Open in new window


Any ideas?

Jess
0
 
jshesekAuthor Commented:
Blends company code should be b123.
And In the result set the business should be in alpha order with the other entries.
0
 
Ryan ChongCommented:
The result set should be ordered by Group name (in alpha order) - then subCompany name (in alpha order.

which field are you referring to as the "Group name"?
0
 
jshesekAuthor Commented:
Companies are either a status of group or subCompany
0
 
Ryan ChongCommented:
sorry, i'm still not clear about your requirement

is the grouping based on Company Number and Group Code (Group Number) ???
0
 
jshesekAuthor Commented:
I solved this.  
- Created a view to pull the current, valid, member data out.
- Next do a query that pulled out all the companies with a "G" in their company code
- In the same query pull the companies that had the same group code in the first company.
I'm sorry I can't post the actual query & view.  But you did help me understand some  issues with it.
0
 
Ryan ChongCommented:
But you did help me understand some  issues with it.

glad that you found the solution.

you may proceed to close or delete this question now.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now