problem on max - 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 2000 Forums

Transact-SQL (2000)

problem on max

Author

Topic

royramos
Starting Member

9 Posts

Posted - 2010-04-15 : 04:49:33

Hi,

i have a new table request with column refnos, requestor and date, my problem is when my refnos reach ref10, when i query it using this "select max(refno) as ref from request" it show ref9, how come ref10 was not seen

-roy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 04:54:27

your refnos is a string

when max() or ordering a string, it is by the ascii code. means A will come first before B, 1 will come first before 9.

And ref10 will comes first before ref9. So when you take the max() of it, the last / max will be "ref9"

What are you trying to achieve here ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 09:05:20

If you have the prefixed format

select 'ref'+cast(ref as varchar(10))) as ref from
(
select max(substring(refno,4,len(refno))*1) as refno from request
) as t

Madhivanan

Failing to plan is Planning to fail

royramos
Starting Member

9 Posts

Posted - 2010-04-15 : 09:35:03

Hi kh,

i want to see the last refnos that was use, how can i do that?

quote:
Originally posted by khtan

your refnos is a string

when max() or ordering a string, it is by the ascii code. means A will come first before B, 1 will come first before 9.

And ref10 will comes first before ref9. So when you take the max() of it, the last / max will be "ref9"

What are you trying to achieve here ?

KH
[spoiler]Time is always against us[/spoiler]

-roy

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-15 : 11:08:17

make your refno field a number

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-15 : 11:39:34

quote:
Originally posted by royramos

Hi kh,

i want to see the last refnos that was use, how can i do that?

quote:
Originally posted by khtan

your refnos is a string

when max() or ordering a string, it is by the ascii code. means A will come first before B, 1 will come first before 9.

And ref10 will comes first before ref9. So when you take the max() of it, the last / max will be "ref9"

What are you trying to achieve here ?

KH
[spoiler]Time is always against us[/spoiler]

-roy

did you try madhi's query ?

KH
[spoiler]Time is always against us[/spoiler]

royramos
Starting Member

9 Posts

Posted - 2010-04-16 : 03:19:21

Hi kh,
yes i tried madhi's query, i recieve syntax error

-roy

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-16 : 03:24:34

quote:
Originally posted by royramos

Hi kh,
yes i tried madhi's query, i recieve syntax error

-roy

A type'O', Now try it

select 'ref'+cast(ref as varchar(10))) as ref from
(
select max(substring(refno,4,len(refno))*1) as refno from request
) as t

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-16 : 04:59:58

quote:
Originally posted by senthil_nagore

quote:
Originally posted by royramos

Hi kh,
yes i tried madhi's query, i recieve syntax error

-roy

A type'O', Now try it

select 'ref'+cast(ref as varchar(10))) as ref from
(
select max(substring(refno,4,len(refno))*1) as refno from request
) as t

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Thanks

royramos, new code is

select 'ref'+cast(ref as varchar(10)) as ref from
(
select max(substring(refno,4,len(refno))*1) as refno from request
) as t

Madhivanan

Failing to plan is Planning to fail

royramos
Starting Member

9 Posts

Posted - 2010-04-19 : 02:54:53

hi madhi and senthil thank you very much for helping me out

i still recieved error now it's Invalid column name 'ref'

-roy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-19 : 03:00:38

[code]
select 'ref'+cast(refno as varchar(10)) as ref from
(
select max(substring(refno,4,len(refno))*1) as refno from request
) as t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-19 : 03:01:54

Post Ur DML Statement!

Whats your column name? refno or ref?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

royramos
Starting Member

9 Posts

Posted - 2010-04-19 : 03:33:10

hi kh, senthil and madhi i already solved the problem thank you very much

-roy

royramos
Starting Member

9 Posts

Posted - 2010-04-19 : 03:50:18

hi kh,

yes that is what i changed

select 'ref'+cast(refno as varchar(10)) as ref from
(
select max(substring(refno,4,len(refno))*1) as refno from request
) as t

thanks all of you who help me
-roy

-roy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources