problem with stored procedure

  • Hi All,

    Iam having clientlist table which contains clients username and the clientversions details.

    Here is the list of Clisntversions of my table:

    2.2.0.12

    2.2.0.13

    2.2.0.16

    2.2.0.18

    2.3.0.1

    2.3.0.2

    From the list we can see the maximum version is 2.3.0.2, but when i ran my SP iam getting the maximum client version is 2.2.0.18. please find the below query and help me on the same :

    DECLARE @Temp as table(ver VARCHAR(20),Major INT,Minor INT,Build INT, Revision INT)

    INSERT INTO @Temp

    SELECT DISTINCT (ClientVersion),NULL,NULL,NULL,NULL FROM ClientList WHERE LoginStatus<>0

    DECLARE @RecCount INT,@MaxMajor INT,@MxCount INT,@MaxMinor INT,@MaxBuild INT,@MaxRevision INT

    UPDATE

    @Temp

    SET Major=[dbo].[udf_Version] (Ver,1)

    UPDATE

    @Temp

    SET Minor=[dbo].[udf_Version] (Ver,2)

    UPDATE

    @Temp

    SET Build=[dbo].[udf_Version] (Ver,3)

    UPDATE

    @Temp

    SET Revision=[dbo].[udf_Version] (Ver,4)

    SELECT @RecCount=COUNT(*) FROM @Temp

    SELECT @MaxMajor=MAX(Major) FROM @Temp

    DECLARE @FinalVal INT

    DECLARE @Ver TABLE(VAL INT)

    DECLARE @Ver1 TABLE(VAL VARCHAR(100))

    INSERT INTO @Ver SELECT DISTINCT MAJOR FROM @Temp

    IF (SELECT COUNT(*) FROM @Ver)=1

    BEGIN

    DELETE FROM @Ver

    SELECT @MaxMinor=MAX(Minor) FROM @Temp

    INSERT INTO @Ver SELECT DISTINCT Minor FROM @Temp

    IF ((SELECT COUNT(*) FROM @Ver)=1) OR ((SELECT COUNT(*) FROM @Temp WHERE Minor=@MaxMinor)>1)

    BEGIN

    DELETE FROM @Ver

    SELECT @MaxBuild =MAX(Build) FROM @Temp

    INSERT INTO @Ver SELECT DISTINCT Build FROM @Temp

    IF ((SELECT COUNT(*) FROM @Ver)=1) OR ((SELECT COUNT(*) FROM @Temp WHERE Build=@MaxBuild)>1)

    BEGIN

    DELETE FROM @Ver

    INSERT INTO @Ver SELECT DISTINCT Revision FROM @Temp

    SELECT @MaxRevision =MAX(Revision) FROM @Temp

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Revision =@MaxRevision

    END

    ELSE

    BEGIN

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Build =@MaxBuild

    END

    END

    ELSE

    BEGIN

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Minor =@MaxMinor

    END

    END

    ELSE

    BEGIN

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Major=@MaxMajor

    END

    SELECT TOP 1 @Version= VAL FROM @Ver1

    END

    Thanks,

    Ram

  • rams.prsk (12/5/2011)


    Hi All,

    Iam having clientlist table which contains clients username and the clientversions details.

    Here is the list of Clisntversions of my table:

    2.2.0.12

    2.2.0.13

    2.2.0.16

    2.2.0.18

    2.3.0.1

    2.3.0.2

    From the list we can see the maximum version is 2.3.0.2, but when i ran my SP iam getting the maximum client version is 2.2.0.18. please find the below query and help me on the same :

    DECLARE @Temp as table(ver VARCHAR(20),Major INT,Minor INT,Build INT, Revision INT)

    INSERT INTO @Temp

    SELECT DISTINCT (ClientVersion),NULL,NULL,NULL,NULL FROM ClientList WHERE LoginStatus<>0

    DECLARE @RecCount INT,@MaxMajor INT,@MxCount INT,@MaxMinor INT,@MaxBuild INT,@MaxRevision INT

    UPDATE

    @Temp

    SET Major=[dbo].[udf_Version] (Ver,1)

    UPDATE

    @Temp

    SET Minor=[dbo].[udf_Version] (Ver,2)

    UPDATE

    @Temp

    SET Build=[dbo].[udf_Version] (Ver,3)

    UPDATE

    @Temp

    SET Revision=[dbo].[udf_Version] (Ver,4)

    SELECT @RecCount=COUNT(*) FROM @Temp

    SELECT @MaxMajor=MAX(Major) FROM @Temp

    DECLARE @FinalVal INT

    DECLARE @Ver TABLE(VAL INT)

    DECLARE @Ver1 TABLE(VAL VARCHAR(100))

    INSERT INTO @Ver SELECT DISTINCT MAJOR FROM @Temp

    IF (SELECT COUNT(*) FROM @Ver)=1

    BEGIN

    DELETE FROM @Ver

    SELECT @MaxMinor=MAX(Minor) FROM @Temp

    INSERT INTO @Ver SELECT DISTINCT Minor FROM @Temp

    IF ((SELECT COUNT(*) FROM @Ver)=1) OR ((SELECT COUNT(*) FROM @Temp WHERE Minor=@MaxMinor)>1)

    BEGIN

    DELETE FROM @Ver

    SELECT @MaxBuild =MAX(Build) FROM @Temp

    INSERT INTO @Ver SELECT DISTINCT Build FROM @Temp

    IF ((SELECT COUNT(*) FROM @Ver)=1) OR ((SELECT COUNT(*) FROM @Temp WHERE Build=@MaxBuild)>1)

    BEGIN

    DELETE FROM @Ver

    INSERT INTO @Ver SELECT DISTINCT Revision FROM @Temp

    SELECT @MaxRevision =MAX(Revision) FROM @Temp

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Revision =@MaxRevision

    END

    ELSE

    BEGIN

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Build =@MaxBuild

    END

    END

    ELSE

    BEGIN

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Minor =@MaxMinor

    END

    END

    ELSE

    BEGIN

    INSERT INTO @Ver1 SELECT ver from @Temp WHERE Major=@MaxMajor

    END

    SELECT TOP 1 @Version= VAL FROM @Ver1

    END

    Thanks,

    Ram

    The first thing I would do is change the DDL for your ClientVersion table to have each segment a different column. Then you can do a simple ORDER BY Major, Minor, Build, Revision.

    Jared

    Jared
    CE - Microsoft

  • Please include the actual DDL from your ClientVersion table, and the function you are calling.

    Jared

    Jared
    CE - Microsoft

  • Also, why are you inserting 3 rows into a table and then selecting TOP 1 without an ORDER BY? Either insert only the 1 row that you need, or make sure that you use ORDER BY in the statement. There is no guaranteed result when you use TOP 1.

    Jared

    Jared
    CE - Microsoft

  • Hai Jared,

    Could you please write me a query which could get the exact maximum versions though there might be many versions in the future.

    Thanks,

    ram

  • rams.prsk (12/5/2011)


    Hai Jared,

    Could you please write me a query which could get the exact maximum versions though there might be many versions in the future.

    Thanks,

    ram

    I can give you a sample query, but only after having the DDL for the table and some sample data.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • Hi,

    Please do find the DDl of my table below :

    ID ClientVersion UserName

    12.5.0.1 ram

    22.6.0.2 suresh

    32.7.1.2 james

    42.5.1.2 raj

    52.3.1.2 rakesh

    62.3.0.2 gouse

    72.7.1.2 likitha

    82.7.2.1 swetha

    Now i want to find out the max version of the clientversion

    Thanks,

    Ram

  • Run this:

    use tempdb

    CREATE TABLE #ClientVersion(id int identity(1,1), ClientVersion varchar(20), UserName varchar(20))

    INSERT INTO #ClientVersion

    SELECT '2.5.0.1','ram'

    UNION ALL

    SELECT '2.6.0.2', 'suresh'

    UNION ALL

    SELECT '2.7.1.2', 'ram'

    UNION ALL

    SELECT '2.5.1.2', 'raj'

    UNION ALL

    SELECT '2.3.1.2', 'ram'

    UNION ALL

    SELECT '2.3.0.2', 'gouse'

    UNION ALL

    SELECT '2.7.1.2', 'gouse'

    UNION ALL

    SELECT '2.7.2.1', 'gouse'

    SELECT * FROM #ClientVersion

    SELECT UserName, MAX(clientversion) FROM #ClientVersion GROUP BY UserName

    DROP TABLE #clientVersion

    The last select (SELECT UserName, MAX(clientversion) FROM #ClientVersion GROUP BY UserName) should give you what you are looking for.

    Jared

    Jared
    CE - Microsoft

  • I'm kind of surprised your max version with that code isn't 2.3.0.18.

    However, it's varchar(). You shouldn't be running into a serious problem ... YET. Not until you're dealing with version 9.x.x.x and 10.x.x.x... at which point everything is going to go to pot.

    Why? See below:

    CREATE TABLE #Tmp1 (Versioning VARCHAR(20) NOT NULL)

    INSERT INTO #Tmp1 VALUES ( '2.2.0.12'),('2.2.0.13'),('2.2.0.16'),('2.2.0.18'),('2.3.0.1'),('2.3.0.2')

    CREATE TABLE #tmp2 (Versioning VARCHAR(20) NOT NULL)

    INSERT INTO #tmp2 VALUES ('9.3.1.18'),('10.0.0.0')

    SELECT MAX( Versioning) AS MaxVer FROM #Tmp1

    SELECT MAX( Versioning) AS MaxVer FROM #Tmp2

    So, my guess is that's why you wrote that very convoluted code to attempt to decipher the values. However, there's an easier way, though the code looks convoluted:

    -- DROP TABLE #tmp1

    CREATE TABLE #Tmp1 (ClientID INT NOT NULL, Versioning VARCHAR(20) NOT NULL)

    INSERT INTO #Tmp1 VALUES (1, '2.2.0.12'),(1,'2.2.0.13'),(1,'2.2.0.16'),(1,'2.2.0.18'),(1,'2.3.0.1'),(1,'2.3.0.2')

    INSERT INTO #Tmp1 VALUES (2, '2.2.0.12'),(2,'2.2.0.13'),(2,'2.2.0.16'),(2,'2.2.0.18'),(2,'2.3.0.1'),(2,'2.3.0.2')

    SELECT

    dst.ClientID,

    ca.Major,

    ca.Minor,

    ca.Build,

    ca.Revision

    FROM

    (SELECT DISTINCT ClientID FROM #tmp1) AS dst

    CROSS APPLY

    (

    SELECT TOP 1

    Major, Minor, Build, Revision

    FROM

    (SELECT

    ClientID,

    CONVERT( INT, PARSENAME( Versioning, 4)) AS Major,

    CONVERT( INT, PARSENAME( Versioning, 3)) AS Minor,

    CONVERT( INT, PARSENAME( Versioning, 2)) AS Build,

    CONVERT( INT, PARSENAME( Versioning, 1)) AS Revision

    FROM

    #Tmp1

    ) AS drv

    WHERE

    drv.ClientID = dst.ClientID

    ORDER BY

    Major DESC, Minor DESC, Build DESC, Revision DESC

    ) AS ca


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Ah yes... 9 to 10. Oops... I still think the best way to do this is to have the columns in the table;

    CREATE TABLE ClientVersion (id int identity(1,1), Version tinyint, SubVersion tinyint, Build tinyint, Revision tinyint, UserName varchar(25))

    If needed, there can be another column with the full version name, but not needed. Then you can simply group by and order by to get the result.

    Jared

    Jared
    CE - Microsoft

  • p-nut (12/5/2011)


    Ah yes... 9 to 10. Oops... I still think the best way to do this is to have the columns in the table;

    CREATE TABLE ClientVersion (id int identity(1,1), Version tinyint, SubVersion tinyint, Build tinyint, Revision tinyint, UserName varchar(25))

    If needed, there can be another column with the full version name, but not needed. Then you can simply group by and order by to get the result.

    Jared

    I see your point Jared, and in this case you're probably right, but this isn't an uncommon scenario. PARSENAME is actually meant for use with 4 part IP addresses, which really are useless independently. Since the ability is there and easy to use, and changing schema usually requires significant code changes, why fight it?

    It's rare you'd want any of that data independently, and it's pretty much atomic as is. It's just frustrating to have to fight it.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply