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 ?
 
Sort by date Sort by votes

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
Longtime 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
Longtime User
Upvote 0

OliverA

Expert
Licensed User
Longtime 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
Longtime User
marcick said:
I have to quote Index (I think it is a reserved word, bad idea to use it as column name)
Click to expand...
I used the unquoted with MySQL and it worked fine.
marcick said:
I'm discovering the problem is the quoting character:
Click to expand...
Yup.
LucaMs said:
The only problem is that max is calculated twice.
Click to expand...
I would say that depends on the "smartness" of the SQL engine.
marcick said:
SELECT LEAST(SELECT MAX(columnname) FROM records, 100);
Click to expand...
This is hilarious:
B4X: 
SELECT LEAST(MAX(Index), 100) FROM records
works.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime 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
You must log in or register to reply here.

Similar Threads

C
  • Question
B4J Question xChart Library Question
Replies
2
Views
227
ChristopherP
C
  • Question
B4J Question [SOLVED] B4XTable - How to get...
Replies
4
Views
846
Mahares
M
  • Question
B4J Question [SOLVED] How To Get Precise B4XTable Column Numbers
2
Replies
22
Views
3K
cklester
P
  • Question
B4J Question MySQL number of records with 3 search parameters[Solved]
Replies
2
Views
737
OliverA
  • Question
B4J Question [B4X] [B4J] [SOLVED] jRotaryKnob set Value
Replies
2
Views
2K
GMan
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…