ORDER BY: A numeric sorting puzzle 1

[Sashanan]

I'm stumped by a sorting order my customer needs on a field containing a multipart numeric code. Code consists of a country code, a letter, a two-digit year code and then an increment, for instance

31S03008

would be a record in the Netherlands (country code 31), and number 008 there in the year 2003.

What the customer wants is to have the records sorted reverse chronologically on year and then on increment. Until recently, our oldest records were from 2001, meaning I could just do

ORDER BY RIGHT(Column1 , 5)

and I'd get results like:

(...)04003
(...)04002
(...)04001
(...)03672
(...)03671
(etc.)


Unfortunately we just imported legacy data going back to 1998, which means that if I do this, records from '98 and '99 appear above those from 2004: after all, '99' is recognized as a higher number than '04'.

Can anybody think of a way I could sort it such that 04, 03, 02, 01, 00 come first and only after that 99, 98? Changing the way those codes are formatted is probably not an option.

Thanks in advance!


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[MakeItSo]

Guess you have to cheat a bit:
In your query, create two field , e.g. Y and Y2

Y: Mid(

,4,2)
Y2: IIf(Y > 90;"1" & Y;"2" & Y)

Sort ascending by Y2.

Background: Y extracts the Year (02,03 or 99)
Y2 adds a 1 or 2 in front of it: (198,199,202)
making years 02,03... larger than 99.

Hope that helps,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP: [URL unfurl="true"]http://www.andygalambos.de[/URL]

 

[MakeItSo]

oh, didn't accept my code + this is ANSI-SQL, so:

SELECT .... Mid(code,4,2) As Y, IIf(Y > 90;"1" & Y;"2" & Y) As Y2 ...

 

[swampBoogie]

order by
case when substring(column1 from 4 for 2) >  '90' then
'19' else '20' end || substring(column1 from 4 for 5)

|| is the standard operator for string concatenation.

 

[r937]

[tt]order by
case when substring(column1 from 4 for 2) > '49'
then '19th century'
else '20th century'
end desc
, substring(column1 from 4 for 2) desc
, substring(column1 from 6 for 3) asc[/tt[

rudy

http://r937.com/

SQL Consulting

 

[Sashanan]

This looks very promising, but unfortunately there's one more complicating factor: the country code is not guaranteed to be 2 characters, and so the year is not guaranteed to begin at the 4th space. It does, however, always begin directly after a letter which is usually S but not always.

However, the year *does* always start 5 characters counting from the right if it's easier to do it from there.


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[r937]

[tt]order by
case when substring(column1
from (length(column1)-4) for 2) > '49'
then '19th century'
else '20th century'
end desc
, right(column1,5) desc[/tt]

rudy

http://r937.com/

SQL Consulting

 

[Sashanan]

Getting an 'incorrect syntax near keyword FROM' on the code from swampBoogie and r937; figured SQL Server was ANSI-compliant but from the look of it I figured wrong. Anything I can change about it to make it work for me?


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[r937]

"figured SQL Server was ANSI-compliant but from the look of it I figured wrong"

yep

use SUBSTRING( expression , start , length )
and LEN( expression )

the rest is the same


rudy

http://r937.com/

SQL Consulting

 

[Sashanan]

Thank you all for the help. I've gone with a slightly different approach in the end (using an assisting column for sorting), but this one did work for me and will definitely come in helpful later.


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)