Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 27th, 2006, 04:25 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Aggregate function MAX creating problem..?



Hope you could help me...

I have a table say [table1] ., in it a column having datatype char of length 10, say [serialno] [char] [10] [allow Null]

I have stored 10 values from 1 to 10 in this column.
when i give query ..

select max(serialno) from table1

it gives me maximum value as 9, whereas it has maximum value as 10
and it should give 10 as a maximum value.

It is happining for even when i am putting any value greater than 9.
It does't show that number as a max no. it is showing 9 as max no.

Why???

Please help me.

Thanks

Regards
Manu


  #2 (permalink)  
Old May 28th, 2006, 03:51 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Because you defined the column as character. Either change the data type or try this
select max(convert(int, serialno)) from table1

From books on line:
Quote:
quote:
Remarks
MAX ignores any null values.

For character columns, MAX finds the highest value in the collating sequence.
  #3 (permalink)  
Old May 29th, 2006, 12:16 AM
Friend of Wrox
 
Join Date: Dec 2004
Location: Chennai, Tamil nadu, India.
Posts: 307
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Vadivel Send a message via Yahoo to Vadivel
Default

Hi,

Just check whether this helps!

-----------Code Snippet Starts here------------
Create Table Tb1 ( sno char(10) )
Go

Insert into Tb1 (sno) values ('1')
Insert into Tb1 (sno) values ('3')
Insert into Tb1 (sno) values ('11')
Go

Select Max(Convert(int, sno) from Tbl1
Go
-----------Code Snippet Ends here------------

Best Regards
Vadivel

http://vadivel.blogspot.com
  #4 (permalink)  
Old May 29th, 2006, 04:59 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your guidance.



  #5 (permalink)  
Old May 30th, 2006, 04:41 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

IT IS DONE !!!!!!!!!!!!!!!!!!!!



Similar Threads
Thread Thread Starter Forum Replies Last Post
aggregate function polofson BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 0 August 14th, 2007 01:29 PM
Using AVG aggregate function with decimal elygp SQL Server 2000 2 May 7th, 2007 09:04 PM
Aggregate Function MAX, SUM Manu SQL Language 2 May 30th, 2006 04:40 AM
Aggregate Function Adamcg Access 3 December 5th, 2005 09:24 AM
Count aggregate function Robert_Hill BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 August 24th, 2004 01:12 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.