B4J Question MySQL experts, come !!

marcick

Well-Known Member
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

OliverA

Expert
Licensed User
Try
B4X:
SELECT IF(MAX(Index) < 100, MAX(Index), 100) FROM records
With parameters
B4X:
SELECT IF(MAX(Index) < ?, MAX(Index), ?) FROM records
You would have to supply the same parameter twice (once for each ?)
 
Upvote 0

OliverA

Expert
Licensed User
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User

upload_2018-10-18_16-2-37.png
 
Upvote 0

OliverA

Expert
Licensed User
Quit quoting Index. That gives MAX a String, not the column name. BTW, did you notice any quotes in my syntax? ;)
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
Finally:

This works fine (with the correct quoting character)

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

Thanks everybody for the support !
 
Upvote 0

OliverA

Expert
Licensed User
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:
B4X:
SELECT LEAST(MAX(Index), 100) FROM records
works.
 
Upvote 0

OliverA

Expert
Licensed User
I take to long to post...
 
Upvote 0

marcick

Well-Known Member
Licensed User
Longtime User
The problem with the quoting character is in PhpMyAdmin. I'll see what happens in JRDC2
 
Upvote 0
Top