December 19, 2008 at 11:14 am
-> 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.
December 19, 2008 at 11:20 am
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?
December 19, 2008 at 11:30 am
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;
December 19, 2008 at 11:34 am
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.
December 19, 2008 at 11:35 am
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###
December 19, 2008 at 11:43 am
If there were a table creation statement and those were insert statements, I know someone that might show you how...
--Jeff Moden
December 19, 2008 at 11:45 am
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;
December 19, 2008 at 11:45 am
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
December 19, 2008 at 11:46 am
And if that works, I could use some more thoughts on a problem I am having...
December 19, 2008 at 11:48 am
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 😀
December 19, 2008 at 11:51 am
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 underneath1
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?
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
December 19, 2008 at 11:58 am
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.
December 19, 2008 at 12:08 pm
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 underneath1
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.
December 19, 2008 at 12:12 pm
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 😎
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
December 19, 2008 at 12:33 pm
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"
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply