DelphiFAQ Home Search:

MAX() in an InterBase query delivers the smallest value


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


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?


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:

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

--->  result set:

/* 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:

Content-type: text/html


2014-12-16, 11:04:52
anonymous from Belgium  
Nanee. Zugolvasf3d vagyok, de most musze1j felszf3lalni, hogy eseneygen bűűűűűűűűűűn feltenni ilyen ke9peket, főleg ha a szerencse9tlen kis jfazer (e9n, me1rmint) az orsze1g me1sik ve9ge9ben lakik, e9s kiscsale1dja egyszerűen csak a hegyekben hajlandf3 nyaralni, me9g ve9letlenfcl se vedzkf6zelben, me9g ve9letlenfcl se a Balcsin...me9g ve9letlenfcl se 'teszfcnk-kite9rőt-kisle1nyom-egy-fagyie9rt', Mama szerint legale1bbis... egyszf3val, most azon morfondedrozok, hogy elkezdjem-e nyalogatni a monitort, vagy se. :D
2015-02-01, 01:11:00
You can always tell an expert! Thanks for conitiburtng.
2015-02-01, 01:13:39
anonymous from Belgium  
The abiitly to think like that is always a joy to behold



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.

photo Add a picture: