Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL: implement greatest / least #2496

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

SQL: implement greatest / least #2496

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2009-05-16 02:54:07 +0200
From: @skinkie
To: SQL devs <>
Version: -- development
CC: dennis, martin.van.dinther

Last updated: 2019-04-30 12:36:02 +0200

Comment 13812

Date: 2009-05-16 02:54:07 +0200
From: @skinkie

For some reason this doesn't work:

sql>select GREATEST(5,3);
!SELECT: no such binary operator 'greatest(tinyint,tinyint)'

I didn't see GREATEST or LEAST in SQL92, but for some reason it seems to be defined in MonetDB anyway.

Comment 13813

Date: 2009-07-09 20:54:00 +0200
From: @grobian

MAX/MIN don't work by chance?

where do GREATEST/LEAST come from?

Comment 13814

Date: 2009-07-09 21:33:11 +0200
From: @skinkie

max/min operate on aggregated results. Greatest/Least results into a value choosen from two (or more) columns.

Comment 13815

Date: 2010-05-04 09:32:09 +0200
From: Pseudo user for Sourceforge import <>

This bug was previously known as tracker item 2792535 at http://sourceforge.net/support/tracker.php?aid=2792535

The original assignee of this bug does not have
an account here. Reassigning to the default assignee
for the component, bugs-sql@monetdb.org.
Previous assignee was nobody@users.sourceforge.net.

Comment 19907

Date: 2014-07-16 11:12:40 +0200
From: Dennis Pallett <>

I'm seconding this request for a GREATEST and a LEAST function. Seems like it should be fairly easy to implement (?).

Comment 21950

Date: 2016-03-24 15:32:40 +0100
From: Martin van Dinther <<martin.van.dinther>>

MonetDB supports the sys.sql_min() and sys.sql_max() scalar functions to determine the greatest or least of two values (or column expressions), e.g:

select sql_min(12345, 5789); // the least of 2 smallint values
select sql_min(12345.12, 5789.12); // the least of 2 decimal values
select sql_min('ab', 'cb'); // the least of 2 char values

select sql_max(12345, 5789); // the greatest of 2 smallint values
select sql_max(12345.12, 5789.12); // the greatest of 2 decimal values
select sql_max('ab', 'cb'); // the greatest of 2 char values

Please note that sql_min() and sql_max():

  • only support 2 arguments, not more (GREATEST and LEAST may have more than 2 arguments).
    Use sql_min(arg1, sql_min(arg2, arg3)) to simulate 3 args equivalent.
  • both arguments must be of the exact same type!
    Use a cast() to make the arguments the same (largest) type, e.g.:
    select sql_min(cast(12 as smallint), 5789);
  • when the first argument is NULL (or expression evaluates to NULL) it returns an error!
  • when the second argument is NULL (or expression evaluates to NULL) it always returns NULL!

Comment 26853

Date: 2019-01-28 10:02:02 +0100
From: MonetDB Mercurial Repository <>

Changeset cac83dfdf816 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=cac83dfdf816

Changeset description:

Implemented two-argument least/greatest functions.
This fixes bug #2496.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant