Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with SQL SELECT MAX

Posted on 2012-03-15
12
Medium Priority
?
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

715 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