Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

Problem with SQL SELECT MAX

Hi,

I'm trying to get the highest customerID number from a table so I can add 1 and have the next number to add a new record.

using "Select MAX (customerID) FROM T_Customers;"

I get a number that is not correct.  The highest customerID number is 11, but this query returns 9.

Thanks
0
taborrg
Asked:
taborrg
  • 7
  • 4
1 Solution
 
costafCommented:
that is wierd, the syntax of the sql command seems to be correct. are you sure ?
what kind of database are you using? where did you run this command?
0
 
taborrgAuthor Commented:
Yes - I've been retrying if for over an hour.  

When I try substituting other fields, it works fine.  Only on the primary key does it not work.

Select   MIN(customerID) FROM T_Customers  gives a "1" as it should.

I even rebooted.
0
 
costafCommented:
have you or anyone else made any changes to that table , that not have been commited yet?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
taborrgAuthor Commented:
No.

Its a pretty simple setup - just me.
0
 
taborrgAuthor Commented:
When I recreate the table with only 5 records it works ok.
0
 
costafCommented:
what kind of data  type is associated with the custtomerid ? try to add more data and try again.
0
 
taborrgAuthor Commented:
did that.  Many times.
0
 
taborrgAuthor Commented:
wait .. might be a type issue.
0
 
bharrington83Commented:
Sounds like you are using a character field as opposed to a numeric field.  It would sort
1
10
11
12
2
3
4
5
7
8
9
0
 
costafCommented:
i aggre with the comment of bharrington83. thats why i asked what data type was associated with the field used in the select max querie.
0
 
taborrgAuthor Commented:
Yep.  That's it

It's working now.
0
 
taborrgAuthor Commented:
costaf,

You did put me onto the solution -  Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now