February 10, 2006 at 12:21 am
Hi, I have the following data that looks like this:
Table A:
userID version#
13302 1.0
13302 1.1
13303 1.0
13304 1.0
Basically, the user 13302 upgraded from version 1.0 to 1.1 (we grab every instance of the userID). I want a list of userID's that are = 1.0, but to not include those userID's that have upgraded (so I don't want 13302 in my list, but if my query states where version#=1.0, it will be included)
Can someone help me write this query? Thanks!
February 10, 2006 at 12:52 am
what is the datatype of column userid and version ?
February 10, 2006 at 12:59 am
Would this do?
select a.userID
from myTable a
where a.version# = 1.0
and not exists ( select * from myTable b where a.userID = b.userID and b.version# > a.version# )
/Kenneth
February 10, 2006 at 8:23 am
sorry,
userID=int
version# is actually versionDescription=varchar(200)
February 10, 2006 at 8:47 am
You should use the folowing Query :----
Select * from TableA WHERE VERSION >=1.0
February 10, 2006 at 9:23 am
So far this is what I have with some of your guy's help:
select a.userID
from sandbox.dbo.ymeUserID2 a with (nolock)
where a.versionDescription = '1.0'
and not exists ( select * from
sandbox.dbo.ymeUserID2 b with (nolock)
where a.userID = b.userID
and b.versionDescription > a.versionDescription )
February 10, 2006 at 9:49 am
If the requirement is to store a version number and then to later implement processes that perform arithmetic comparisons on that number, then a varchar(200) stuffed with version descriptions like "version 1.0" is the wrong design.
You will find yourself writing complex CharIndex() or PatIndex() expressions trying to pull the numbers out of the description strings.
Model the data correctly as what it represents - a number. Or keep your description column, but add a new column for the version number.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply