Order by numbers in proper sequence

Question

Post reply

Order by numbers in proper sequence

Viewing 15 posts - 31 through 45 (of 52 total)

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

J-440512 SSCertifiable Points: 6285 More actions · Answer 1

-> dejanette.gordon

The only thing missing is the data generator...

[font="Courier New"]INSERT INTO myTable (Field1, Field2, etc.)

SELECT '10', '20' UNION ALL

SELECT '11', '21' UNION ALL

SELECT '12', '22' UNION ALL[/font]

As opposed to a SELECT Field1, Field2 FROM tbl_myTable, which we do not have. You could easily generate the data generator by doing something like

SELECT 'SELECT ', CHAR(39) + CONVERT(varchar(10), Field1) + CHAR(39), ', ', CHAR(39) + CONVERT(varchar(10), Field2) + CHAR(39), 'UNION ALL'

FROM tbl_myTable

This way, we do not have to retype in the data you use. Makes life easier for all of us and easier to help you.

I suppose that "OP" stands for Original Post author...

And by the way, I absolutely DETEST the "tbl" prefix in front of a table name (a pet peeve).

Hope we are helping you.

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

Here is the snippets of data from your post:

A & H RESTAURANT & BAR SUPPLY PA 6
AD-ART SIGN COMPANY LV 11
AD-ART SIGN COMPANY PA 11
ALLIED REFRIGERATION, INC. PA 15
ANDERSON DAIRY PRODUCTS PA 19
ALLEN-BAILEY TAG & LABEL, INC PA 24
SOURCE 4 INDUSTRIES, INC. PA 25
BONANZA BEVERAGE COMPANY PA 32
CAMP DAVID, INC. R00042 PA
CHRISTY-GARRISON CO. R00046 PA
ROYAL PACIFIC ENTERPRISES, INC R00072 PA
VANTAGE CUSTOM CLASSICS, INC. R00099 PA
ASHWORTH, INC. R00166 PA
CAPO DEMONT, INC. R00172 PA
SMITH-WESTERN CO. R00173 PA
CARTA MUNDI, INC. R00188 PA
OURI INDUSTRIES R00190 PA
NIKE GOLF R00199 PA
MOYNA LLC R0020 PA

With some manual formatting, is this how you want to see the output based on the above?

A & H RESTAURANT & BAR SUPPLY 6 PA

AD-ART SIGN COMPANY 11 LV

AD-ART SIGN COMPANY 11 PA

ALLIED REFRIGERATION, INC. 15 PA

ANDERSON DAIRY PRODUCTS 19 PA

MOYNA LLC R0020 PA

ALLEN-BAILEY TAG & LABEL, INC 24 PA

SOURCE 4 INDUSTRIES, INC. 25 PA

BONANZA BEVERAGE COMPANY 32 PA

CAMP DAVID, INC. R00042 PA

CHRISTY-GARRISON CO. R00046 PA

ROYAL PACIFIC ENTERPRISES, INC R00072 PA

VANTAGE CUSTOM CLASSICS, INC. R00099 PA

ASHWORTH, INC. R00166 PA

CAPO DEMONT, INC. R00172 PA

SMITH-WESTERN CO. R00173 PA

CARTA MUNDI, INC. R00188 PA

OURI INDUSTRIES R00190 PA

NIKE GOLF R00199 PA

And, is there anything else we should know about numbers we are sorting on, other than some are pure numeric and others are prefixed with an R?

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

Using my last post as my working theory, here is some code to work with:

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 cast(substring(CompanyID,2,len(CompanyID) - 1) as int)

else cast(CompanyID as int)

end;

drop table #MyTest;

J-440512 SSCertifiable Points: 6285 More actions · Answer 4

The code posted by Lynn

[font="Courier New"]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[/font]

is exactly what YOU should be providing. You can thank Lynn for having patiently done the work for you.

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

All of the values are numberic and 85 rows of data have the leading R. The sorting I am looking for is all the numbers in numberic order 1st, and then the R#'s lined up right underneath

1

2

3

4

5

R0020

R0042

R0046

R00101

R00199

etc...

All of the R-Supplier codes are region type PA and all of the R Values begin like R00###

Jeff Moden SSC Guru Points: 1003961 More actions · Answer 6

If there were a table creation statement and those were insert statements, I know someone that might show you how...

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

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;

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

Thank you so much Lynn! I copied the code that you provided and now I have much more to work with, as well as another viable solution. :D:) thanks soooooooooooo much. I've learned so much from posting and I really appreciate your help and expertise

Thanks again! D Gordon

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

And if that works, I could use some more thoughts on a problem I am having...

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

That was perfect! and EXACTLY what I needed. I'd be glad to help with anything if I am able to...I'm so thankful to you in this moment Lynn 😀

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

dejanette.gordon (12/19/2008)
All of the values are numberic and 85 rows of data have the leading R. The sorting I am looking for is all the numbers in numberic order 1st, and then the R#'s lined up right underneath
1
2
3
4
5
R0020
R0042
R0046
R00101
R00199
etc...
All of the R-Supplier codes are region type PA and all of the R Values begin like R00###

Where do '001' and '100' fit in?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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 Pettis SSC Guru Points: 442467 More actions · Answer 13

Chris Morris (12/19/2008)
dejanette.gordon (12/19/2008)
All of the values are numberic and 85 rows of data have the leading R. The sorting I am looking for is all the numbers in numberic order 1st, and then the R#'s lined up right underneath
1
2
3
4
5
R0020
R0042
R0046
R00101
R00199
etc...
All of the R-Supplier codes are region type PA and all of the R Values begin like R00###
Where do '001' and '100' fit in?

I don't think it did. I think she was generalizing her problem instead of giving us what was really going on. It is really just a learning curve when it comes to asking for assistance on forums like this. If you haven't done it before or very often, how do you ask, how much info do you provide?

That is why it is a good idea for us to get new posters to read the article Jeff wrote. That is an excellent starting point for new users.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Lynn Pettis (12/19/2008)
I think she was generalizing her problem instead of giving us what was really going on. It is really just a learning curve when it comes to asking for assistance on forums like this. If you haven't done it before or very often, how do you ask, how much info do you provide?
That is why it is a good idea for us to get new posters to read the article Jeff wrote. That is an excellent starting point for new users.

That's so true 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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"