TSQL PRoblm
-
Now he's got one more
. -
Tadaa!!!!
SELECT z.part1 + '-' + CONVERT(VARCHAR, z.part2) refNo
FROM (
SELECT PARSENAME(REPLACE(refNo, '-', '.'), 2) part1,
MAX(CONVERT(INT, PARSENAME(REPLACE(refNo, '-', '.'), 1))) part2
FROM YourTable
GROUP BY PARSENAME(REPLACE(refNo, '-', '.'), 2)
) z
ORDER BY 1
N 56°04'39.16"
E 12°55'05.25" -
Darn, you beat me to the parsename method.
(just increasing my postcount)
/Kenneth
-
The only problem with that solution is that if a number is written like this abc012, your query will return abc12.
That's why I wrote mine that way... might not be necessary but just in case
. Also now I rechecked my version against Eric's and mine is faster with the added index (since Eric didn't provide one ) -
I say we keep this thread going until we've got, say, 6 more ways of solving this problem - OR - until San replies. Whichever comes first.
:o)
-
That'll take too long.. The longest threads here have gone far in the 100s so I got other stuff to do
. -
I considered the same solution Peter L, but encountered the problem of lopping off leading zeros. That's actually a common mistake that is included in report tester scripts. These aren't even leading zeros we're looking at, so it really is unacceptable.
-
Why limit it to a SQL 2000 solution ?
Create Table #t ( RefNo Varchar(20) not null)
insert #t
select 'ABC-0' union all
select 'ABC-1' union all
select 'XYZ-0' union all
select 'XYZ-1' union all
select 'XYZ-02' union all
select 'PQR-0' union all
select 'MNO-0' union all
select 'MNO-1' union all
select 'SPX-0' union all
select 'SPX-1'
WITH RefNoCTE (RefNo,AlphaRef,NumRef)
AS
(
Select RefNo,
Left(RefNo, Dash-1) As AlphaRef,
Cast(Substring(RefNo, Dash+1, DataLength(RefNo)) As int) As NumRef
From (
Select RefNo, CharIndex('-', RefNo) As Dash
From #t
) dt
)
Select RefNo
From RefNoCTE As r
Inner Join
(
Select AlphaRef, Max(NumRef) As MaxNum
From RefNoCTE
Group By AlphaRef
) dt
On (dt.AlphaRef = r.AlphaRef And
dt.MaxNum = r.NumRef)
-
How about someone provides some oracle solutions while I come up with the Access 2K version?
-
Don't make me fire up my old BTrieve workstation ...
-
Damn, I'm still to green to understand that joke
. -
This is the 'Post of the Week' !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Well, I think everyone likes a little challenge.
Personally, I like a little champagne. Right about now. It's Friday, after all.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply