March 21, 2011 at 6:22 pm
Hey fellow SQL'ian's,
I have a quandary. It appears that my code is not doing what I told it too... Go figure!
select max(id) from phys
It should pull the largest id number which is 3.7 million, but it is actually pulling 99999. After reviewing the table, the smallest number is 50, but it shows up in the middle of the pack.
From your experience, how or what can I do to make SQL order correctly (like a sort on Excel)? Any thoughts?
Thanks in advance!!! 😀
March 21, 2011 at 7:26 pm
What is the datatype for the id column?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 21, 2011 at 8:05 pm
Assuming that Jeffrey is correct and that the id field is a character field, and it only has numbers in it you could try this:
select max(cast(id as INTEGER)) from phys
You may have to use BIGINT, or a NUMBER format depending on the data you have.
March 21, 2011 at 9:37 pm
Jeffery,
It is a varchar(15) datatype
March 21, 2011 at 9:54 pm
The problem (as you have probably already figured out from my previous post) is the ID field can, and does, contain numbers and values (such 1, 2, 6, MD4, XX2, etc.)
This is not only causing problems in SQL, but also in my Java code as well....:crazy:
But that's another forum...
March 21, 2011 at 11:33 pm
Then how do you define MAX?
You could always pad the values with 0:
select max(RIGHT('000000000000000' + ID, 15)) from phys
That may get you closer to what you are looking for. (Of course it will be much slower.)
If you only want the MAX of numbers you could add a WHERE clause using LIKE:
SELECT
MAX(RIGHT('000000000000000' + ID, 15))
FROM phys
WHERE
ID NOT LIKE '%[^0-9.]%'
(In that I am only allowing 0-9 and . as characters that count as a number. That is only very basic validation of it being a number, so make sure you know what will work for what you want and your data.)
March 22, 2011 at 9:51 am
This helps... thank you to everyone for the postings.
As always, greatly appreciated...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply