DelphiFAQ Home Search:
General :: Databases :: InterBase
Help with InterBase, Borland's open source database.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

MAX() in an InterBase query delivers the smallest value

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

Generated 8:04:28 on Jan 22, 2019