SQL SELECT MAX problem

SQL SELECT MAX problem

Post by Ken » Fri, 28 Nov 2003 11:16:26



I am using the routine:
SELECT ID FROM TABLE where ID=(SELECT MAX(ID) FROM TABLE)

to obtain the maximum ID in the table, then ID = ID + 1
to insert a new record with new ID.  However, after
creating a record with ID = 9, it continued giving the
same number.  Therefore new number is always 10.  

This happened again when another ID became 99.  It
continued creating recordset with ID = 100.  

What causes this problem?  How can I overcome it?  
I would really appreciate any help on this one.

 
 
 

SQL SELECT MAX problem

Post by Jacco Schalkwij » Fri, 28 Nov 2003 12:16:22


Hi Ken,

You have make the ID column an INT instead of a VARCHAR. VARCHARs are
ordered alphabetically, so 9 will be the maximum until you get to 90.
If you can't  change the datatype of the ID column, change your code to:

SELECT MAX(CONVERT(INT, ID)) FROM TABLE

--
Jacco Schalkwijk
SQL Server MVP


Quote:> I am using the routine:
> SELECT ID FROM TABLE where ID=(SELECT MAX(ID) FROM TABLE)

> to obtain the maximum ID in the table, then ID = ID + 1
> to insert a new record with new ID.  However, after
> creating a record with ID = 9, it continued giving the
> same number.  Therefore new number is always 10.

> This happened again when another ID became 99.  It
> continued creating recordset with ID = 100.

> What causes this problem?  How can I overcome it?
> I would really appreciate any help on this one.


 
 
 

SQL SELECT MAX problem

Post by Ken » Sat, 29 Nov 2003 02:44:27


Thank you, Jacco.  That's exactly the problem I had.  I
just used Clng(ID) instead of Convert and it worked
perfectly.  I appreciate it.
Ken
Quote:>-----Original Message-----
>Hi Ken,

>You have make the ID column an INT instead of a VARCHAR.
VARCHARs are
>ordered alphabetically, so 9 will be the maximum until
you get to 90.
>If you can't  change the datatype of the ID column,

change your code to:

>SELECT MAX(CONVERT(INT, ID)) FROM TABLE

>--
>Jacco Schalkwijk
>SQL Server MVP


message

>> I am using the routine:
>> SELECT ID FROM TABLE where ID=(SELECT MAX(ID) FROM
TABLE)

>> to obtain the maximum ID in the table, then ID = ID + 1
>> to insert a new record with new ID.  However, after
>> creating a record with ID = 9, it continued giving the
>> same number.  Therefore new number is always 10.

>> This happened again when another ID became 99.  It
>> continued creating recordset with ID = 100.

>> What causes this problem?  How can I overcome it?
>> I would really appreciate any help on this one.

>.

 
 
 

SQL SELECT MAX problem

Post by Ken » Sun, 30 Nov 2003 12:24:51


Just clarify for some folks who may review this,
actually, I used the same code as Jacco suggested.  Clng
(ID) was not good for SQL.  
Thanks.
Ken
>-----Original Message-----
>Thank you, Jacco.  That's exactly the problem I had.  I
>just used Clng(ID) instead of Convert and it worked
>perfectly.  I appreciate it.
>Ken
>>-----Original Message-----
>>Hi Ken,

>>You have make the ID column an INT instead of a
VARCHAR.
>VARCHARs are
>>ordered alphabetically, so 9 will be the maximum until
>you get to 90.
>>If you can't  change the datatype of the ID column,
>change your code to:

>>SELECT MAX(CONVERT(INT, ID)) FROM TABLE

>>--
>>Jacco Schalkwijk
>>SQL Server MVP


>message

>>> I am using the routine:
>>> SELECT ID FROM TABLE where ID=(SELECT MAX(ID) FROM
>TABLE)

>>> to obtain the maximum ID in the table, then ID = ID +
1
>>> to insert a new record with new ID.  However, after
>>> creating a record with ID = 9, it continued giving the
>>> same number.  Therefore new number is always 10.

>>> This happened again when another ID became 99.  It
>>> continued creating recordset with ID = 100.

>>> What causes this problem?  How can I overcome it?
>>> I would really appreciate any help on this one.

>>.

>.

 
 
 

1. MS Access 97 SQL to MS SQL SQL SQL SQL

Does anyone know of a good utility to convert simple MS Access 97 (SQL view)
queries to ones usable on an MS SQL server. I believe there are symbol
differences and stuff. If there isn't a utility could someone point me in
the right direction of a good explanation of the differences.

Thanks,

Oli T.

2. CLOB -> BLOB Conversion

3. SQL problem, MSDTC Problem or VB.NET problem?

4. Table Names

5. SQL-DMO Problem in T-SQL in SQL Server 7 with SP3

6. 'Query is too complex..' error

7. SET IDENTITY_INSERT and Identify Column Problem in SQL 7.0

8. SQL-92, Migrate PL/SQL at ORACLE into PL/SQL at SQL Server 7.0

9. Problems with Select Max on Date Field

10. Selecting MAX(date) problems

11. problem selecting max value

12. query select max(date) performance problem