February 9, 2009 at 12:24 pm
I need to find the max value of a version - but if I use select max(version) it's only looking at the first alpha and returns 'z' if I have values in the AB or higher range.
Any ideas? (it can't be more than 2 characters....max of zz)
Version
A
B
C
....
Z
AA
AB
AC
AD
....
AZ
BA
BB
(etc)
February 9, 2009 at 12:29 pm
You might try something like this:
create table #T (
Ver char(10) primary key);
insert into #T (Ver)
select 'A' union all
select 'B' union all
select 'AA';
select *
from #T
order by reverse(ver) desc;
Returns:
AA
B
A
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 12:37 pm
nope - that doesn't work.
Let's say I have a version "BT" (and everything below ....from A to Z, AA to AZ, and BA to BT)
I need to find something I can write in SQL to retrieve the BT version.
Should I split it up and get the max of the left alpha and then the max of the right and then join together? I just don't know where to go with this....
February 9, 2009 at 12:56 pm
You're right. Didn't think it through quite far enough. Try this instead:
create table #T (
Ver char(10) primary key);
declare @Alpha char(26)
select @Alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
insert into #T (Ver)
select substring(@Alpha, number, 1)
from dbo.Numbers
where number between 1 and 26
insert into #T (Ver)
select 'A' + substring(@Alpha, number, 1)
from dbo.Numbers
where number between 1 and 26
insert into #T (Ver)
select 'B' + substring(@Alpha, number, 1)
from dbo.Numbers
where number between 1 and 20
select *
from #T
order by len(ver) desc, substring(ver, 1, 1) desc, substring(ver, 2, 1) desc
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 1:01 pm
Almost there!!!! Thank you gsquared!
I have:
select ver AS TOPVER from mytable
where partnumber = 'P7029'
order by len(ver) desc, substring(ver, 1, 1) desc, substring(ver, 2, 1) desc
which works and puts the version I want on top - so how do I do a select statement to retrieve that one value that is on top of my result set?
February 9, 2009 at 1:31 pm
n_parker (2/9/2009)
Almost there!!!! Thank you gsquared!I have:
select ver AS TOPVER from mytable
where partnumber = 'P7029'
order by len(ver) desc, substring(ver, 1, 1) desc, substring(ver, 2, 1) desc
which works and puts the version I want on top - so how do I do a select statement to retrieve that one value that is on top of my result set?
select TOP (1) ver AS TOPVER from mytable
where partnumber = 'P7029'
order by len(ver) desc, substring(ver, 1, 1) desc, substring(ver, 2, 1) desc
* Noel
February 9, 2009 at 1:42 pm
It's returning: Line 3:Incorrect syntax near '('.
when I copy what you wrote.
Update: got rid of the parens around the one and it worked - thanks!!!
February 10, 2009 at 8:03 am
Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply