December 5, 2011 at 10:38 am
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
December 5, 2011 at 10:44 am
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
December 5, 2011 at 10:46 am
Please include the actual DDL from your ClientVersion table, and the function you are calling.
Jared
Jared
CE - Microsoft
December 5, 2011 at 10:48 am
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
December 5, 2011 at 10:52 am
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
December 5, 2011 at 10:53 am
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
December 5, 2011 at 10:59 am
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
December 5, 2011 at 11:08 am
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
December 5, 2011 at 11:08 am
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
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
December 5, 2011 at 11:15 am
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
December 5, 2011 at 11:35 am
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.
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