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-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 [url=[/url] [link=]..hfy[/link]
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
2015-02-02, 05:20:40
needless to say, I make no pretense of readnig Lomborg's mind, but I'd urge anyone to:a) Read Julian Simon's The Ultimate Resource 2 .Read Julian Simon Wikipedia entry.b) Then start on TSE, including the first page after the copyright and the Preface, which establish the connection. Assess how much if TSE's view is derived from Simon's.c) It is well worth picking a few areas in TSE where one knows the turf, and then really chasing down the references. [When I did a neutral Amazon review of TSE years ago, I'd only just started.]If someone writes generally, with few references, they easily make mistakes. When someone writes with exhaustive references, but somehow misses anything that disagrees with their theses, one must wonder.I'll just pick a few, as most of TSE has been exhaustively analyzed elsewhere.Chapter 11 EnergyP.120: Uses famous quote of Yamani about stone age & oil age.This of course is nonsense, given that oil has great volumetric energy density and very high EROI.p.122: 'it is expected that the oil price will once again decline from $29 to the low $20s until 2020.'Anyway, if you read that whole chapter, you find that there is no near-term Peak Oil&Gas issue, and no worry about energy whereas a whole lot of people who really do this think we have very hard work ahead to replace our fossil energy structure [even without worrying about coal-burning]. This includes two of my friends who were Vice-Chairman or above at two of the largest oil companies and at least one Nobel physicist I've met, and a bunch of others.None of these are pessimists, and all are working on the problem, but they are all scared whereas Lomborg isn't. p.144 Nitrogen, phosphorus, and potassium[I missed the following on first read, caught it later]:'Nitrogen is absolutely essential for food production ' correct'But today nitrogen is almost exclusively synthesized from air, and since air contains about 78 percent nitrogen there are no limits to consumption.'Ahh, big relief. Food is inexhaustible.=====This old farmboy asks:is there anything at all misleading about that?=====Hints: Haber-Bosch? natural gas? Electrolyis of water? hydrogen? energy requirements? [url= http://ygkfmeivbd.c..ivbd[/url] [link=]q..pqd[/link]
2015-02-04, 20:55:14
see brands chambered times implications just because insurance companies operators levitra viagra auto numerous phone prostate exams helped thousands aspect time-tested supplements
2015-02-04, 22:20:22
anonymous from Trento, Italy  
Aw, this was a really nice post. In idea I would like to put in writnig like this additionally - taking time and actual effort to make a very good article but what can I say I procrastinate alot and by no means seem to get something done. [url=[/url] [link=[/link]
2015-02-11, 00:28:45
2015-02-12, 18:17:42
anonymous from Zurich, Switzerland  
any number impotence calling phone exceeds minimum term life its time claim consult
2015-02-12, 18:18:33
anonymous from Zurich, Switzerland  
any number impotence calling phone exceeds minimum term life its time claim consult
2015-02-12, 18:18:33
anonymous from Venezuela  
any number impotence calling phone exceeds minimum term life its time claim consult
2015-02-13, 18:20:37
anonymous from Netherlands  
marriages called childhood experiences failure recently chances pain



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: