SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORDER By Based on Company then SubCompany


ORDER By Based on Company then SubCompany

Author
Message
jslist
jslist
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 15
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
Thom A
Thom A
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80131 Visits: 20347
Whata have you tried so far?


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211093 Visits: 24520
Add a suitable ORDER BY clause to your SQL statement.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jacob Wilkins
Jacob Wilkins
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24705 Visits: 13062
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!
drew.allen
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62309 Visits: 16629
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82840 Visits: 9353
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jacob Wilkins
Jacob Wilkins
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24705 Visits: 13062
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!
drew.allen
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62309 Visits: 16629
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859623 Visits: 47093
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Joe Celko
Joe Celko
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6265 Visits: 3488
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search