July 3, 2002 at 9:27 am
I've got a fairly easy to solve problem, that i can't solve..!
My statment:
SELECT unqueid, title, initials, Surname, Position, company, address1, address2, address3, town, address5, postcode, paid FROM ifa WHERE postcode LIKE 'N%' AND postcode LIKE '_[ 0123456789]%' ORDER BY paid DESC, postcode ASC
returns everything whose postcode starts 'N' and whose second character is numeric;
the problem is some of these numbers are two digit and some are one.
The obvious problem ensues that they are incorrectly ordered, i.e 1,10,11,12,2 instead of 1,2,...11,12.
Does anyone have any ideas how i can achive this?
Cheers
July 3, 2002 at 10:02 am
cast( col as int) in the order by. This will move 1,10,2 to 1,2,10.
If this is the second character (and beyond)
cast( substring( col, 2, 10) as int)
Steve Jones
July 3, 2002 at 10:03 am
The only thing I can think of is to massage your data before you return it... however, this might be too expensive if thier is a high number of rows being returned.
SELECT convert(int,substring(postcode,2,1) as intvalue, convert(int,isnumeric(substring(postcode,3,1))) as isnumpos3, unqueid, title, initials, Surname, Position, company, address1, address2, address3, town, address5, postcode, paid into #tmp1 FROM ifa WHERE postcode LIKE 'N%' AND postcode LIKE '_[ 0123456789]%' ORDER BY paid DESC, postcode ASC
update #tmp1 set intvalue = convert(int,convert(varchar(1),intvalue) + '0') + convert(int,substring(postcode,3,1)) where isnumpos3 = 1
select unqueid, title, initials, Surname, Position, company, address1, address2, address3, town, address5, postcode, paid from #tmp1order by paid DESC, intvalue ASC
July 3, 2002 at 10:07 am
Cheers both.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply