• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Brian HarringtonIT ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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