Order by numbers in proper sequence

Question

Order by numbers in proper sequence

Viewing 8 posts - 46 through 52 (of 52 total)

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

dejanette.varnado Right there with Babe Points: 718 More actions · Answer 1

Oh, you are right Lynn, I cannot help you out with that problem. I'm only a 'grasshopper' well at least according to the rating SSC gives newbies...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

dejanette.gordon (12/19/2008)
You are both correct; I was just generalizing the numbering sequence issue because I was trying to be thorough by providing some type of example. But you made it very clear that you needed an accurate look at the data rather than a random sample. I do appreciate you ‘schooling’ me on how to post properly 😉 Some mistakes only need to be made ONCE before you "get it"

This is where "knowing the data" comes in to play when working a problem. Nothing wrong with using "made up" data as long as it properly mirrors your actual data. A good example here would be the medical field. You can't use real people in this environment, but you could make up a set of sample data that mirrors your actual data that you can post and have people work with. You then take what they provide and use it internally on your actual data.

I'd have the same issue here with some of our personnel or student data.

Jeffrey Williams SSC Guru Points: 90267 More actions · Answer 3

Lynn Pettis (12/19/2008)
Dejanette,
I am sure you would help if you could. My problem is with applying SP 3 to a multi-instance x64 server running SQL Server 2005 Developer Edition.
If you can't help, that's fine, but there are others out there with greater experience that could provide helpful insights.

Lynn - did you really mean to post that here? :w00t:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

dejanette.varnado Right there with Babe Points: 718 More actions · Answer 4

I see. 😀

Lynn...I want to genuinely thank you for taking an above and beyond extra step to assist me in this matter. In spite of my folly, you extended kindness towards me. That speaks volumes to your character:exclamation: That might sound very mushy but I mean it. Thanks a bunch 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Jeffrey Williams (12/19/2008)
Lynn Pettis (12/19/2008)
Dejanette,
I am sure you would help if you could. My problem is with applying SP 3 to a multi-instance x64 server running SQL Server 2005 Developer Edition.
If you can't help, that's fine, but there are others out there with greater experience that could provide helpful insights.
Lynn - did you really mean to post that here? :w00t:

Yes. Several people that I have great faith in have seen this thread, but may not have seen mine yet, and this may give them a little prod to see if they have any ideas that may help me. I've given up for a bit as of now, though, I'm sure I am have a forest and trees issue now and just can't see anything clearly.

Plus, she said she would help if she could, and I just wanted her to know what I was working on, and that I did't really think she would be one to assist in that area. Doesn't mean she couldn't ask a co-worker what they thought of the problem.

Never know where that spark may come from that helps solve a problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

dejanette.gordon (12/19/2008)
I see. 😀
Lynn...I want to genuinely thank you for taking an above and beyond extra step to assist me in this matter. In spite of my folly, you extended kindness towards me. That speaks volumes to your character:exclamation: That might sound very mushy but I mean it. Thanks a bunch 😀

I've been told (in other posts) that I'm too nice (way too nice one person said). You needed help, I was able to help, and I am glad I was of help to you.

Now, keep on posting and asking for help as you need it. Also, take the time to try and answer other peoples questions as well. You will find you learn even more as you try to help others. That is what has happened with me on this site. As you work on a problem you do research that you might not otherwise do and increase your own knowledge at the same time.

Also, read the articles, especially the ones in my signature block. Jeff Moden is an awesome mentor.

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 7

Lynn Pettis (12/19/2008)
Okay, hows this??
create table #MyTest (
CompanyName varchar(50),
CompanyID varchar(10),
RegionID char(2)
);
insert into #MyTest
select 'ANDERSON DAIRY PRODUCTS ','19','PA' union all
select 'ALLIED REFRIGERATION, INC. ','15','PA' union all
select 'MOYNA LLC ','R0020','PA' union all
select 'CAMP DAVID, INC. ','R00042','PA' union all
select 'AD-ART SIGN COMPANY ','11','PA' union all
select 'CHRISTY-GARRISON CO. ','R00046','PA' union all
select 'ROYAL PACIFIC ENTERPRISES, INC ','R00072','PA' union all
select 'VANTAGE CUSTOM CLASSICS, INC. ','R00099','PA' union all
select 'A & H RESTAURANT & BAR SUPPLY ','6','PA' union all
select 'ASHWORTH, INC. ','R00166','PA' union all
select 'ALLEN-BAILEY TAG & LABEL, INC ','24','PA' union all
select 'CAPO DEMONT, INC. ','R00172','PA' union all
select 'SMITH-WESTERN CO. ','R00173','PA' union all
select 'SOURCE 4 INDUSTRIES, INC. ','25','PA' union all
select 'BONANZA BEVERAGE COMPANY ','32','PA' union all
select 'CARTA MUNDI, INC. ','R00188','PA' union all
select 'AD-ART SIGN COMPANY ','11','LV' union all
select 'OURI INDUSTRIES ','R00190','PA' union all
select 'NIKE GOLF ','R00199','PA';
select
*
from
#MyTest
order by
case when left(CompanyID,1) = 'R'
then stuff(CompanyID,2,0,replicate('0', 10 - len(CompanyID))) --cast(substring(CompanyID,2,len(CompanyID) - 1) as int)
else right('0000000000' + CompanyID, 10)
end;
drop table #MyTest;

Dude... NOT YOU! 😉

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Jeff Moden (12/19/2008)
Lynn Pettis (12/19/2008)
Okay, hows this??
create table #MyTest (
CompanyName varchar(50),
CompanyID varchar(10),
RegionID char(2)
);
insert into #MyTest
select 'ANDERSON DAIRY PRODUCTS ','19','PA' union all
select 'ALLIED REFRIGERATION, INC. ','15','PA' union all
select 'MOYNA LLC ','R0020','PA' union all
select 'CAMP DAVID, INC. ','R00042','PA' union all
select 'AD-ART SIGN COMPANY ','11','PA' union all
select 'CHRISTY-GARRISON CO. ','R00046','PA' union all
select 'ROYAL PACIFIC ENTERPRISES, INC ','R00072','PA' union all
select 'VANTAGE CUSTOM CLASSICS, INC. ','R00099','PA' union all
select 'A & H RESTAURANT & BAR SUPPLY ','6','PA' union all
select 'ASHWORTH, INC. ','R00166','PA' union all
select 'ALLEN-BAILEY TAG & LABEL, INC ','24','PA' union all
select 'CAPO DEMONT, INC. ','R00172','PA' union all
select 'SMITH-WESTERN CO. ','R00173','PA' union all
select 'SOURCE 4 INDUSTRIES, INC. ','25','PA' union all
select 'BONANZA BEVERAGE COMPANY ','32','PA' union all
select 'CARTA MUNDI, INC. ','R00188','PA' union all
select 'AD-ART SIGN COMPANY ','11','LV' union all
select 'OURI INDUSTRIES ','R00190','PA' union all
select 'NIKE GOLF ','R00199','PA';
select
*
from
#MyTest
order by
case when left(CompanyID,1) = 'R'
then stuff(CompanyID,2,0,replicate('0', 10 - len(CompanyID))) --cast(substring(CompanyID,2,len(CompanyID) - 1) as int)
else right('0000000000' + CompanyID, 10)
end;
drop table #MyTest;
Dude... NOT YOU! 😉

What??? 😀