MAX() in an InterBase query delivers the smallest value

Databases
InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)

MAX() in an InterBase query delivers the smallest value

This article has not been rated yet. After reading, feel free to leave comments and rate it.

Question:

I run the following query (see box below) and it returns the expected results.
Then I want to retrieve only the max() value and it returns the smallest value instead. Why is that?

Answer:

I looked into the problem and suspected that the ID field 'units_scenarios_id' is not a numerical field. Indeed it was defined as a varchar().
You can add +0 to force Interbase to treat it as a number or do a clean type cast as shown below:

select max(cast(FIELDNAME as integer))

/* this query returns 4 rows - proper results */
select u2.units_scenarios_id
from units_scenarios u1, units_scenarios u2
where u1.units_scenarios_id = 1971547
and u2.unit_id = u1.unit_id
and u2.units_scenarios_id <> u1.units_scenarios_id

-->  result set:
48167
1800458
1971810

/* --- now just retrieve the max() one:   */
select max(u2.units_scenarios_id)
..

--->  result set:
48167


/* SOLUTION: turns out that units_scenarios_id is varchar(32) */
select max(u2.units_scenarios_id+0)
..

/* or this one - cleaner with a CAST */
select max(cast(u2.units_scenarios_id as integer))
..


--->  result set:
1971810

Send a message to
Subject

Comments:

NEW: Optional: Register Login Email address (not necessary): Rate as	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option, or post under a registered account.

	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material. Add a picture:


	Search: