Max Number - 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)

Max Number

Author

Topic

josethegeek
Starting Member

45 Posts

Posted - 2004-12-17 : 17:44:54

Maybe somebody can help me out with a problem I can't seem to solve. I would like to get the Max number from a field in our database that begin with 2 specific numbers.

For example we have orders that begin with 11 and I would like to get the Max order number that begins with the number 11.

Any help would be greatly appreciated.

Thanks In Advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-17 : 17:49:50

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE '11%'

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-17 : 17:53:02

And if you want to use variables:

DECLARE @StartsWith char(2)

SET @StartsWith = '11'

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE @StartsWith + '%'

Tara

josethegeek
Starting Member

45 Posts

Posted - 2004-12-17 : 17:57:48

quote:
Originally posted by tduggan

And if you want to use variables:

DECLARE @StartsWith char(2)

SET @StartsWith = '11'

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE @StartsWith + '%'

Tara

tduggan,
Thanks for the quick response! Would this matter if OrderNumber was a varchar? What if we had 2 records (112346, 112345678), wouldn't 112345 be consider the bigger number when in reality 112345678 is the bigger number? Would I need to convert this records to an int datatype?

Thanks Again

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-17 : 18:32:25

quote:
Originally posted by josethegeek

quote:
Originally posted by tduggan

And if you want to use variables:

DECLARE @StartsWith char(2)

SET @StartsWith = '11'

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE @StartsWith + '%'

Tara

tduggan,
Thanks for the quick response! Would this matter if OrderNumber was a varchar? What if we had 2 records (112346, 112345678), wouldn't 112345 be consider the bigger number when in reality 112345678 is the bigger number? Would I need to convert this records to an int datatype?

Thanks Again

It's OK, man. U should try first before asking, it's simple

josethegeek
Starting Member

45 Posts

Posted - 2004-12-17 : 20:09:59

quote:
Originally posted by Hippi

quote:
Originally posted by josethegeek

quote:
Originally posted by tduggan

And if you want to use variables:

DECLARE @StartsWith char(2)

SET @StartsWith = '11'

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE @StartsWith + '%'

Tara

tduggan,
Thanks for the quick response! Would this matter if OrderNumber was a varchar? What if we had 2 records (112346, 112345678), wouldn't 112345 be consider the bigger number when in reality 112345678 is the bigger number? Would I need to convert this records to an int datatype?

Thanks Again

It's OK, man. U should try first before asking, it's simple

Hippi,
I know what the results would be. I was hoping someone could show me a work around with the problem. By stating it as a questions, maybe they could explain the work around a little more in depth.
At least that was what I was hoping for.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-18 : 10:45:32

What are the requirements? You start out by saying "I would like to get the Max number from a field". Is the column a number or is it a string? Is the string always numeric? How should the Max be determined? These issues need to be defined.

HTH

=================================================================

Happy Holidays!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-18 : 16:30:47

Yes, if you want these compared numerically rather than alphabetically, then you need to convert them to numbers. Short of actually converting the field definition, you can do the conversion on the fly, but it's a bit convoluted to do a conversion and an aggregation on the same field. I had to use a subquery to get it to work. Check this out:


CREATE TABLE #Temp (
  NumberData  int,
  StringData  varchar(20)
)

INSERT INTO #Temp (NumberData, StringData) VALUES (112346, '112346')
INSERT INTO #Temp (NumberData, StringData) VALUES (112345678, '112345678')

SELECT max(NumberData) as MaxNum, max(StringData) as MaxString
FROM #Temp

SELECT max(t2.StringToNum) as MaxStringToNum
FROM (SELECT convert(int, StringData)as StringToNum FROM #Temp) as t2

DROP TABLE #Temp

Edit: Removed unnecessary GOs
---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-18 : 18:30:25

Yes, that was a bit convoluted

,
whats wrong with:

SELECT MAX(CAST(col AS dt)) FROM tbl

rockmoose

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-30 : 14:59:35

quote:
Originally posted by rockmoose

Yes, that was a bit convoluted ,
whats wrong with:

SELECT MAX(CAST(col AS dt)) FROM tbl

rockmoose

You mean other than it should be converting to int?

Actually, that was the approach I first tried but couldn't get the right result, I must have mixed up my columns or something. Thought SQL Server was being bizarrely temperamental.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-12-30 : 15:29:26

quote:
Originally posted by josethegeek

quote:
Originally posted by tduggan

And if you want to use variables:

DECLARE @StartsWith char(2)

SET @StartsWith = '11'

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE @StartsWith + '%'

Tara

tduggan,
Thanks for the quick response! Would this matter if OrderNumber was a varchar? What if we had 2 records (112346, 112345678), wouldn't 112345 be consider the bigger number when in reality 112345678 is the bigger number? Would I need to convert this records to an int datatype?

Thanks Again

Why is OrderNumber a varchar? Do the first couple of digits represent something about the order?

josethegeek
Starting Member

45 Posts

Posted - 2004-12-30 : 16:35:47

quote:
Originally posted by DustinMichaels

quote:
Originally posted by josethegeek

quote:
Originally posted by tduggan

And if you want to use variables:

DECLARE @StartsWith char(2)

SET @StartsWith = '11'

SELECT MAX(OrderNumber)
FROM Table1
WHERE OrderNumber LIKE @StartsWith + '%'

Tara

tduggan,
Thanks for the quick response! Would this matter if OrderNumber was a varchar? What if we had 2 records (112346, 112345678), wouldn't 112345 be consider the bigger number when in reality 112345678 is the bigger number? Would I need to convert this records to an int datatype?

Thanks Again

Why is OrderNumber a varchar? Do the first couple of digits represent something about the order?

Thank To All that replied. We found a workaround this, and it's working now.

jhermiz

3564 Posts

Posted - 2004-12-30 : 19:24:22

can you post it ?

A new beat on the web -- http://www.web-impulse.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources