B4J Question MySQL experts, come !!

[marcick]

I know yhis is not the correct place, but I googled much without success ...

I need to find the minimum value between the maximum value in a column and a constant

In my mnd the sintax would be this but doesn't work

SELECT LEAST(SELECT MAX(columnname) FROM records, 100);

Any help ?

 

[BillMeyer]

Correct Syntax would be:

SELECT MIN(Price) AS SmallestPrice FROM Products;

SELECT MAX(Price) AS LargestPrice FROM Products;

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

So let's put it all together:

Select Min(columnname) From table_Name where columnname BETWEEN 1 AND 100 - (1 and 100 you now again can substitute with any variable you like)

More Information: https://www.w3schools.com/sql/default.asp

Hope this helps

 

[marcick]

Hi, probably I didn't explain well.

I need the minimum value between two object

object 1: is the maximum value of the primary key of my table. I obtain this with SELECT MAX('Index') FROM records
object 2: is a number that i pass as parameter in JRDC2 command. So it is a '?' in a command inside config.propertis file of JRDC2

 

[LucaMs]

SELECT MIN(MAX('Index'), 100) FROM records

 

[marcick]

SELECT MIN(MAX('Index'), 100) FROM records

eh si, purtroppo da errore di sintassi .....
Looks good, but give a sintax error ....

 

[OliverA]

Try

SELECT IF(MAX(Index) < 100, MAX(Index), 100) FROM records

With parameters

SELECT IF(MAX(Index) < ?, MAX(Index), ?) FROM records

You would have to supply the same parameter twice (once for each ?)

 

[marcick]

Try

SELECT IF(MAX(Index) < 100, MAX(Index), 100) FROM records

I think we are close to the solution, but still this does not work

 

[OliverA]

but still this does not work

Such as?

 

[marcick]

Such as?

 

[OliverA]

Quit quoting Index. That gives MAX a String, not the column name. BTW, did you notice any quotes in my syntax?

 

[LucaMs]

It works. The only problem is that max is calculated twice.

 

[marcick]

I have to quote Index (I think it is a reserved word, bad idea to use it as column name)

If I write SELECT MAX(Index) FROM record give an error

I'm discovering the problem is the quoting character:

SELECT IF(MAX(`Index`) < 50, MAX(`Index`), 50) FROM records works
SELECT IF(MAX('Index') < 50, MAX('Index'), 50) FROM records does not work

 

[marcick]

Finally:

This works fine (with the correct quoting character)

SELECT LEAST(MAX(`Index`), 10) FROM records

Thanks everybody for the support !

 

[OliverA]

I have to quote Index (I think it is a reserved word, bad idea to use it as column name)

I used the unquoted with MySQL and it worked fine.

I'm discovering the problem is the quoting character:

Yup.

The only problem is that max is calculated twice.

I would say that depends on the "smartness" of the SQL engine.

SELECT LEAST(SELECT MAX(columnname) FROM records, 100);

This is hilarious:

SELECT LEAST(MAX(Index), 100) FROM records

works.

 

[OliverA]

I take to long to post...

 

[LucaMs]

 

[marcick]

The problem with the quoting character is in PhpMyAdmin. I'll see what happens in JRDC2

 

[LucaMs]

MS Sql Server accepts square brackets too !

Damn syntax

 

[LucaMs]

MS Sql Server accepts square brackets too !

Damn syntax

.... and this:

SELECT MIN(MAX('Index'), 100) FROM records

works on SQLite.

 

[marcick]

MS Sql Server accepts square brackets too !

Damn syntax

What is amazing with SQL is that any condition you have in mind can be done. Looks like there are no limits. Just have to find the sintax ...