Solved

Problem with SQL SELECT MAX

Posted on 2012-03-15
12
455 Views
Last Modified: 2012-03-15
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
Comment
Question by:taborrg
  • 7
  • 4
12 Comments
 
LVL 2

Expert Comment

by:costaf
ID: 37724318
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
 
LVL 1

Author Comment

by:taborrg
ID: 37724333
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
 
LVL 2

Expert Comment

by:costaf
ID: 37724341
have you or anyone else made any changes to that table , that not have been commited yet?
0
 
LVL 1

Author Comment

by:taborrg
ID: 37724349
No.

Its a pretty simple setup - just me.
0
 
LVL 1

Author Comment

by:taborrg
ID: 37724371
When I recreate the table with only 5 records it works ok.
0
 
LVL 2

Expert Comment

by:costaf
ID: 37724381
what kind of data  type is associated with the custtomerid ? try to add more data and try again.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:taborrg
ID: 37724387
did that.  Many times.
0
 
LVL 1

Author Comment

by:taborrg
ID: 37724392
wait .. might be a type issue.
0
 
LVL 9

Expert Comment

by:bharrington83
ID: 37724401
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
 
LVL 2

Accepted Solution

by:
costaf earned 500 total points
ID: 37724406
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
 
LVL 1

Author Comment

by:taborrg
ID: 37724407
Yep.  That's it

It's working now.
0
 
LVL 1

Author Closing Comment

by:taborrg
ID: 37724414
costaf,

You did put me onto the solution -  Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now