MySQL Is it possible to set LIMIT argument to a dynamic result of SELECT statement?

[Giggles]

Is it possible to do this something similar to this:

SELECT column1 FROM table1 LIMIT (SELECT column1 FROM table2 WHERE column3="whatever");

I wonder if you can use only one statement for such action, BETWEEN allows to use values that result from other statements like this:

BETWEEN (statement1) AND (statement2)

 

[metalfan49]

It should work, I think it suppports all sorts of nested queries. I expect your sub-statement would have to return a single number though, since that would be what limit is expecting. Something like this maybe:

SELECT column1 FROM table1 LIMIT (SELECT count(column1) FROM table2 WHERE column3="whatever");

 

[BiggieShady]

I might be wrong but according to documentation you are limited to parameters and local variables http://dev.mysql.com/doc/refman/5.7/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
  
  
• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.
  

I'd do something like this:

SELECT LimitValue
INTO @myLimit
FROM LimitsTable
WHERE ID = 1;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @myLimit ;

 

[metalfan49]

I might be wrong but according to documentation you are limited to parameters and local variables http://dev.mysql.com/doc/refman/5.7/en/select.html

I'd do something like this:

SELECT LimitValue
INTO @myLimit
FROM LimitsTable
WHERE ID = 1;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @myLimit ;

Sounds like the way to go.

Tried the single query approach here : https://www.tutorialspoint.com/mysql_terminal_online.php , and had no luck
MariaDB [CODINGGROUND]> select * from users limit (select count(*) from users where sex like "F");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to
use near '(select count(*) from users where sex like "F")' at line 1

 

[Aquinus]

A little more info about what the OP is doing could be important because this feels like an improper use of LIMIT in the current context and I will explain why. Limit is supposed to reflect that you want so many of the data you're querying.

SELECT column1 FROM table1 LIMIT (SELECT column1 FROM table2 WHERE column3="whatever");

This is particularly weird because if I were convert this to English, you would get something like this:

Give me column1 from table1 and give me as many records as there are in table2 with column3 having "whatever" for a value.

What this gets you is any record from table1 but, limited (by count,) by the number of records in table2 that fit the criteria. Since this isn't a JOIN, the rows you get from table1 are mostly non-deterministic and as a result, will give you a pseudo-random (but, certainly not completely random,) sample of your data.

You would be better off doing something more like this if the number of rows in table1 are to match the rows in table2, even if they're not selected.

SELECT a.column1 FROM table1 AS a JOIN table2 AS b ON a.column1 = b.column1 WHERE b.column2 = 'foobar' GROUP BY a.column1;

This would provide you unique values from column1 in table1 for every match in table2 given the WHERE constraint of b.column2 being set to "foobar". There is absolutely no reason why you need to limit to an upper bound of something you're joining against. Something like this might also be valid:

SELECT a.column1 FROM table1 AS a WHERE a.column1 IN (SELECT b.column1 FROM table2 AS b WHERE b.column2 = 'foobar');

Now, I don't usually use MySQL. PostgreSQL is my weapon of choice but, everything tells me that the approach of using LIMIT for this purpose is wrong.

select * from users limit (select count(*) from users where sex like "F");

Just as a FYI, that says give me as many users without any filter criteria up to the same number of users that are female. If it worked, it would not get all users that are female.

 

[FordGT90Concept]

SELECT a.column1 FROM table1 AS a JOIN table2 AS b ON a.column1 = b.column1 WHERE b.column2 = 'foobar' GROUP BY a.column1;

This hits the nail on the head. I'd omit the "AS" clause though because that just makes things confusing. There has to be a column in table2 that associates with table1 (some cross referencing ID). That ON clause ties them up. The WHERE clause can be on a column in table1 and/or table2.

Here's a functional example I used in one of my programs:
SELECT recording.idRecording, recording.fileName, schedule.programName FROM recording LEFT JOIN schedule ON recording.idSchedule=schedule.id_Schedule WHERE isRecording=0 AND title='manual'; // Get list of all recordings titled "manual" along with their correct programName

Syntax highlighting:
column names
comment
MySQL keywords
table name

LIMIT should only be used to break a larger query into parts for easier reading or to guarantee no more than x rows are returned (e.g. code is designed to only handle one row at a time should have LIMIT 1 so it doesn't overflow).

 

[Aquinus]

This hits the nail on the head. I'd omit the "AS" clause though because that just makes things confusing. There has to be a column in table2 that associates with table1 (some cross referencing ID). That ON clause ties them up. The WHERE clause can be on a column in table1 and/or table2.

If the column in both tables is the same name, you must reference the table directly which is why I used the aliases because one letter is much less text than a full table name. So if you had the column name "column1" on both tables, you would have to spell out the entire table name if you don't alias it. I'm used to working with queries that join up north of 6 tables, all of which have relatively long names which is why I did it off the bat. I would turn something like user_log to "ul" just to keep the query short and simple. I personally feel aliasing is more readable and easier to manage than spelling out the table name every time you need to reference a column.

Either way, the example the OP gave was incredibly generic but despite how generic it was, there really should be no reason to use "LIMIT" this way.

 

[BiggieShady]

there really should be no reason to use "LIMIT" this way.

The only reason I can fathom is having default limit for paging and such in a configurable setting database table and use it in all queries as parameter ... but again there are better ways to do that also

 

[Aquinus]

The only reason I can fathom is having default limit for paging and such in a configurable setting database table and use it in all queries as parameter ... but again there are better ways to do that also

Sure but, I would argue that should be managed at the application level, even if it's stored in another table. Numerical count limits usually don't need to be dynamic in this way as there is usually a more proper way to do it.

 

[BiggieShady]

Sure but, I would argue that should be managed at the application level, even if it's stored in another table. Numerical count limits usually don't need to be dynamic in this way as there is usually a more proper way to do it.

Exactly, every client should be able to control page size through backend REST api ... properly
Just trying to get what OP meant because LIMIT is used for paging

 

[FordGT90Concept]

Yeah, LIMIT is either hard coded to prevent overflowing or a local variable the application uses. It shouldn't be used as a constraint to only retrieve specific rows from a table.

 

[BiggieShady]

Yeah, LIMIT is either hard coded to prevent overflowing or a local variable the application uses. It shouldn't be used as a constraint to only retrieve specific rows from a table.

It's common to write:

SELECT * FROM HugeTable LIMIT @pageSize * (@pageNumber - 1), @pageSize

and get page size and page number form the client (and having defaults if they are missing)

 

[Aquinus]

It's common to write:

SELECT * FROM HugeTable LIMIT @pageSize * (@pageNumber - 1), @pageSize

and get page size and page number form the client

In PostgreSQL it's not unrealistic to provide it as a query argument provided by the application.

SELECT * FROM HugeTable LIMIT $1 OFFSET $2;

In Clojure using clojure.java.jdbc, it would look something like this:

(let [db-conn (get-db-conn) page 1 page-size 10]
  (clojure.java.jdbc/query db-conn ["SELECT * FROM HugeTable LIMIT ? OFFSET ?" page-size (* page-size (- page 1))]))

Side note: Clojure and PostgreSQL is an explosive combination in my professional opinion.