SQL MAX Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL MAX Function

Author

Topic

Buda56
Starting Member

14 Posts

Posted - 2011-06-14 : 22:28:27

Hi,
I have been using the following SQL query and it has been working fine up until just recently (last couple of days)

SELECT MIN(Sig_Version) AS Min_Sig, REPLACE(MIN(Sig_Version), '.', '') AS nMin_Sig, MAX(Sig_Version) AS Max_Sig, REPLACE(MAX(Sig_Version), '.', '') AS nMax_Sig,
MIN(AV_Version) AS Min_Prod, REPLACE(MIN(AV_Version), '.', '') AS nMin_Prod, MAX(AV_Version) AS Max_Prod, REPLACE(MAX(AV_Version), '.', '') AS nMax_Prod,
AV_Product
FROM dbo.vwSWD_AV AS vsa
GROUP BY AV_Product

Min_Sig nMin_Sig Max_Sig nMax_Sig Min_Prod nMin_Prod Max_Prod nMax_Prod AV_Product
0.0.0.0 0000 N/A N/A 8.1.637 81637 8.1.655 81655 CA eTrustITM Agent
36.1.0.8384 36108384 36.1.0.8385 36108385 8.1.637 81637 8.1.637 81637 CA eTrustITM Remote Install
N/A N/A N/A N/A N/A N/A N/A N/A No Supported AV installed
31.6.6482 3166482 36.1.8331 3618331 7.1.0192 710192 7.1.0192 710192 CA eTrust Antivirus
N/A N/A N/A N/A N/A N/A N/A N/A Not Reported
5400.1158 54001158 5400.1158 54001158 8.7.0 870 8.8.00000 8800000 McAfee VirusScan Enterprise

Now for some reason the MAX value of CA etRustITM Agent shows as N/A when thre is actually a value, the only thing that I can see is that the Sig Version for both the CA eTrustITM Agent and the CA eTrustITM Remote Install are the same values in the database (36.1.0.8385)

Any help would be greatly appreciated

Regards..
Peter

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-15 : 01:37:11

If you see really 'N/A' as value then 'N/A' is stored in the table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Buda56
Starting Member

14 Posts

Posted - 2011-06-15 : 03:11:15

Hi,
Thanks for that, there was really an N/A stored in one row in the table. Not sure why MAX sees this as being higher than a text number which is stored in the rest of the rows for that column.

Regards..
Peter

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-15 : 03:29:15

Have a look at the ASCII table and you will see why MAX() sees this higher...

No, you're never too old to Yak'n'Roll if you're too young to die.

Buda56
Starting Member

14 Posts

Posted - 2011-06-15 : 03:43:39

Yep my mistake.. text numbers are lower than letters.

Regards..
Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources