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.

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

Comments:

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-01-01, 01:13:16
anonymous from Venezuela  
Kedves Max!Volt szerencse9m megkf3stolni a he9tve9ge9n, ahogy az olive1s faigyt is : ) Mindkettő kfclf6nleges edze9lme9ny volt. Ezfaton kf6szf6nf6m a kedvesse9ged, f6nzetlense9ged! Ne haragudj, hogy nem tudtalak megve1rni, a gyerekek annyira nyűgf6sek voltak, hogy nem bedrte1k tove1bb a ve1rakoze1st. Me9gegyszer kf6szf6nf6m a lehetőse9get a kf3szole1sra, fcdvf6zlettel hűse9ges olvasf3d, Piroska http://wyhabeuogoa.com [url= http://qrckjwtqp.co..wtqp[/url] [link= http://jzuphfy.com]..hfy[/link]
2015-02-01, 01:11:00
anonymous  
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: