Order by numbers in proper sequence

Question

Post reply

Order by numbers in proper sequence

Viewing 15 posts - 16 through 30 (of 52 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Janette,

Problem is: my solution

ORDER BY AlphanumericValue

works perfectly for the dataset you provided:

100

101

102

etc...

Wanna better answer - take some care about asking better question.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

dejanette.gordon (12/18/2008)
It sure is...it can be downright grueling and discouraging for a newbie if one takes it personally. But hey, my post was kind of simple and "cheesy" based on how it was supposed to look. I didn't know any better, but I trust me, you will NEVER see me post without following the standards 😛 I was cracking up at the last post from Jeff about entertainment...LOL You've gotta take it all in stride though. This will only help me out as well as the next newbie (not sure if you get many around here??) from being made an example of...still LOL at myself.

And, yet, you still haven't posted the real data you want the help with... 😛 Remember this?

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value [font="Arial Black"]'R00190'[/font] to data type int.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

SELECT *

FROM #MyTest

ORDER BY LEN(TestData), TestData

N 56°04'39.16"
E 12°55'05.25"

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

Peso,

I was interested enough in your idea, I tried the following: (note the result: 1 comes before 001).

[font="Courier New"]create table #T (pk int not null primary key identity, dat varchar(10))

insert into #T (dat)

SELECT '1' union SELECT '001' union SELECT '11' union SELECT '100' union SELECT 'R00102' union SELECT '00102' union SELECT '0102'

select * from #T order by len(dat), dat

(7 row(s) affected)

pkdat

41

611

1001

5100

30102

200102

7R00102

(7 row(s) affected)

select * from #T

pkdat

1001

200102

30102

41

5100

611

7R00102

(7 row(s) affected)[/font]

Close, but no cigar.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

And what is the expected output based on your sample data?

N 56°04'39.16"
E 12°55'05.25"

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

Peso,

That is indeed the question:

For ALPHANUMERIC data, does '001' come before '1' ?.

I cannot shake out of my head that anything that sarts with 0 should come before that starts with 1 -- as IF this was numeric data ...

I once worked for a big company that had a product catalog, without prices (so you could give the product catalog to customers) and an internal only list of prices for each product - you guessed it, order by product part number, so every one was looking for 11 was quite miffed to find it after 100... This did not work out too well.

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

J (12/19/2008)
Peso,
I was interested enough in your idea, I tried the following: (note the result: 1 comes before 001).
Close, but no cigar.

The only known part of the required sequence is as follows

'100'

'101'

'102'

Provided Peso's code maintains this sequence, which can only be assumed to extend to '999', then he gets a cigar. The OP hasn't indicated where '001' fits relative to '1' (or '100', or 'a', or even '@$$').

^{“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

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

Pressed "Post Reply" too quickly.

pk dat

1 001

2 00102

3 0102

4 1

6 11

5 100

7 R00102

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

Chris,

As shown in my real-life example, the OP data set is only a very restrictive of a larger, PRACTICAL set where length is not fixed. Besides, the OP also mentioned "R0102' or something like that...

I got interested in this discussion because i have encountered this problem (not solved yet, on the back burner), for the general case. Still hoping for someone to suggest something elegant.

So the cigar was to be from me, not from the OP. Guess I hijacked the thread a bit.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

declare @t table (pk int not null primary key identity, dat varchar(10))

insert into @t (dat)

select '1' union all select '001' union all select '11' union all

select '100' union all select ' 00972' union all select ' 00472' union all

select 'X00472' union all select 'R00102' union all select '00102' union all select '0102'

select *

from@t

order by

-- Sort all numbers first (containing only digits characters 0-9) followed by non-numbers

case when dat like '%[^0-9]%' then convert(bigint,0x7fffffffffffffff) else dat end,

-- Sort by varchar value after primary sort

dat

Results:

pk dat

----------- ----------

2 001

1 1

3 11

4 100

9 00102

10 0102

6 00472

5 00972

8 R00102

7 X00472

(10 row(s) affected)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 11

DECLARE@Sample TABLE

(

pk INT,

dat VARCHAR(20)

)

INSERT@Sample

SELECT4, '1' UNION ALL

SELECT2, '00102' UNION ALL

SELECT6, '11' UNION ALL

SELECT1, '001' UNION ALL

SELECT3, '0102' UNION ALL

SELECT5, '100' UNION ALL

SELECT7, 'R00102'

-- Peso

SELECTpk,

dat

FROM@Sample

ORDER BYPATINDEX('%[^0]%', dat) DESC,

CASE

WHEN dat LIKE '%[^0-9]%' THEN 2147483647

ELSE CAST(dat AS INT)

END,

dat

N 56°04'39.16"
E 12°55'05.25"

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

J (12/19/2008)
Chris,
As shown in my real-life example, the OP data set is only a very restrictive of a larger, PRACTICAL set where length is not fixed. Besides, the OP also mentioned "R0102' or something like that...
I got interested in this discussion because i have encountered this problem (not solved yet, on the back burner), for the general case. Still hoping for someone to suggest something elegant.
So the cigar was to be from me, not from the OP. Guess I hijacked the thread a bit.

It does get frustrating when several people chip in with their ideas, often complementary, and there's no feedback from the OP to indicate if people are on the right track. Grr! Have a beer for trying.

I like this, which I first saw on SSC: ORDER BY RIGHT('000'+'A'), where the number of zero's is one less than the length of the column. It works for most situations.

^{“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

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

Gave it another try. Using the slash character, its ASCII value is 1 less than that of the 0 (zero) character. Works as long as the slash characgter is not part of the data set.

I'll look into Peso's last.

[font="Courier New"]CREATE TABLE #T (pk int not null primary key identity, dat varchar(10), SortKey char(10))

INSERT INTO #T (dat)

SELECT '1'

UNION SELECT '001'

UNION SELECT '11'

UNION SELECT '100'

UNION SELECT '0001'

UNION SELECT 'R00102'

UNION SELECT 'R0/102'

UNION SELECT '/R00102'

UNION SELECT '00102'

UNION SELECT '0102'

UPDATE #T SET SortKey = REPLICATE('/', 10 - LEN(dat)) + dat

UPDATE #T SET SortKey = REPLACE('//', '/0', SortKey) WHERE SortKey LIKE '%/0%'

SELECT dat from #T order by SortKey

dat

0001

001

00102

0102

1

11

100

R0/102

R00102

/R00102[/font]

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

Here I am...the "OP" I presume? . I still haven't had the time to follow the posting forum document completely which was the #1 requirement. I am still testing the script in between doing all the other tasks I have to do at work. I'm currently testing this portion:

SELECT 'SELECT'

+ QUOTENAME(CompanyRegion,'''')+','

+ QUOTENAME(dateadded,'''')+','

+ QUOTENAME(SupplierCode,'''')+','

+ QUOTENAME(TransactionAmount,'''')+','

+ ' UNION ALL' FROM tblVendorTotals

I should have been more specific in my post...anyway here is the workaround I'm using in the meantime so that I could publish the report to the report server.

SELECT SUPPLIERNAME,CompanyRegion,SUPPLIERCODE

FROM tblVendorTotals

WHERE SUPPLIERCODE NOT LIKE 'R%'

GROUP BY SUPPLIERCODE,CompanyRegion,SUPPLIERNAME HAVING COUNT (*) = 1

ORDER BY CAST(SupplierCode as int),CompanyRegion

Snipet of output:

A & H RESTAURANT & BAR SUPPLYPA6

AD-ART SIGN COMPANY LV 11

AD-ART SIGN COMPANY PA 11

ALLIED REFRIGERATION, INC.PA15

ANDERSON DAIRY PRODUCTSPA19

ALLEN-BAILEY TAG & LABEL, INCPA24

SOURCE 4 INDUSTRIES, INC. PA25

BONANZA BEVERAGE COMPANYPA32

I was going to Union the query above with this one so that I can seperate the non-numeric values from the numberics. this will only work if I don't need to order the values.

SELECT SUPPLIERNAME,SUPPLIERCODE,CompanyRegion

FROM tblVendorTotals

WHERE SUPPLIERCODE LIKE 'R%'--84

GROUP BY SUPPLIERCODE,CompanyRegion,SUPPLIERNAME HAVING COUNT (*)=1

ORDER BY SupplierCode--,suppliername

Snipet of output:

CAMP DAVID, INC. R00042PA

CHRISTY-GARRISON CO. R00046PA

ROYAL PACIFIC ENTERPRISES, INCR00072PA

VANTAGE CUSTOM CLASSICS, INC.R00099PA

ASHWORTH, INC. R00166PA

CAPO DEMONT, INC.R00172PA

SMITH-WESTERN CO.R00173PA

CARTA MUNDI, INC.R00188PA

OURI INDUSTRIES R00190PA

NIKE GOLF R00199PA

MOYNA LLC R0020PA

I'm curious to know ifI'd been waaaay more specific (I thought I was at the time) in the beginning, would I still have been given a solution or would I still have needed to create a test table so that the forum could re-create this very common ORDER BY issue? And yes, I'll comply and send the dataset etc... but I'm just curious...

Ah, don't have a beer on my account... have one because it is Friday!

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

Peso,

Thanks for your last.

As a bonus, you reminded me to use "UNION ALL" instead of just "UNION". Granted that for a small set of 8 lin es it does not matter performance-wise, but I should keep that in mind whenever I use the UNION construct.