how to set the value of LIMIT using select count(id) from another table |MySQL
up vote
1
down vote
favorite
I have a scenario where the result must be limited depends on counting ids in another table.
Suppose i have these two tables counter
and dispanser
,
i want to select
the last records in the table counter
and limit the selection by counting the number of records in dispanser
table.
something like this
select * from counter limit (select count(dispID) from dispanser)
mysql select limit
add a comment |
up vote
1
down vote
favorite
I have a scenario where the result must be limited depends on counting ids in another table.
Suppose i have these two tables counter
and dispanser
,
i want to select
the last records in the table counter
and limit the selection by counting the number of records in dispanser
table.
something like this
select * from counter limit (select count(dispID) from dispanser)
mysql select limit
I got no idea, but found your question interesting. Why would you like to do that?
– luisfer
Nov 9 at 23:46
Is there any relationship between the data in thecounter
anddispanser
tables - i.e. doesdispanser
have acounterId
field or something similar?
– Jpec07
Nov 9 at 23:51
@luisfer because i want the last records in table counter, and thelast
is a dynamic value so, i need each time to count
– Aly Al Ameen
Nov 9 at 23:55
@Jpec07 yescounter
table hasdispID
– Aly Al Ameen
Nov 9 at 23:55
Do you have access to MySQL version >= 8.0.2 ?
– Madhur Bhaiya
Nov 10 at 9:11
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a scenario where the result must be limited depends on counting ids in another table.
Suppose i have these two tables counter
and dispanser
,
i want to select
the last records in the table counter
and limit the selection by counting the number of records in dispanser
table.
something like this
select * from counter limit (select count(dispID) from dispanser)
mysql select limit
I have a scenario where the result must be limited depends on counting ids in another table.
Suppose i have these two tables counter
and dispanser
,
i want to select
the last records in the table counter
and limit the selection by counting the number of records in dispanser
table.
something like this
select * from counter limit (select count(dispID) from dispanser)
mysql select limit
mysql select limit
asked Nov 9 at 23:41
Aly Al Ameen
15119
15119
I got no idea, but found your question interesting. Why would you like to do that?
– luisfer
Nov 9 at 23:46
Is there any relationship between the data in thecounter
anddispanser
tables - i.e. doesdispanser
have acounterId
field or something similar?
– Jpec07
Nov 9 at 23:51
@luisfer because i want the last records in table counter, and thelast
is a dynamic value so, i need each time to count
– Aly Al Ameen
Nov 9 at 23:55
@Jpec07 yescounter
table hasdispID
– Aly Al Ameen
Nov 9 at 23:55
Do you have access to MySQL version >= 8.0.2 ?
– Madhur Bhaiya
Nov 10 at 9:11
add a comment |
I got no idea, but found your question interesting. Why would you like to do that?
– luisfer
Nov 9 at 23:46
Is there any relationship between the data in thecounter
anddispanser
tables - i.e. doesdispanser
have acounterId
field or something similar?
– Jpec07
Nov 9 at 23:51
@luisfer because i want the last records in table counter, and thelast
is a dynamic value so, i need each time to count
– Aly Al Ameen
Nov 9 at 23:55
@Jpec07 yescounter
table hasdispID
– Aly Al Ameen
Nov 9 at 23:55
Do you have access to MySQL version >= 8.0.2 ?
– Madhur Bhaiya
Nov 10 at 9:11
I got no idea, but found your question interesting. Why would you like to do that?
– luisfer
Nov 9 at 23:46
I got no idea, but found your question interesting. Why would you like to do that?
– luisfer
Nov 9 at 23:46
Is there any relationship between the data in the
counter
and dispanser
tables - i.e. does dispanser
have a counterId
field or something similar?– Jpec07
Nov 9 at 23:51
Is there any relationship between the data in the
counter
and dispanser
tables - i.e. does dispanser
have a counterId
field or something similar?– Jpec07
Nov 9 at 23:51
@luisfer because i want the last records in table counter, and the
last
is a dynamic value so, i need each time to count– Aly Al Ameen
Nov 9 at 23:55
@luisfer because i want the last records in table counter, and the
last
is a dynamic value so, i need each time to count– Aly Al Ameen
Nov 9 at 23:55
@Jpec07 yes
counter
table has dispID
– Aly Al Ameen
Nov 9 at 23:55
@Jpec07 yes
counter
table has dispID
– Aly Al Ameen
Nov 9 at 23:55
Do you have access to MySQL version >= 8.0.2 ?
– Madhur Bhaiya
Nov 10 at 9:11
Do you have access to MySQL version >= 8.0.2 ?
– Madhur Bhaiya
Nov 10 at 9:11
add a comment |
3 Answers
3
active
oldest
votes
up vote
0
down vote
accepted
You can't do this without using prepared statements or a stored procedure. From the manual:
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants
In a stored procedure you could do something like this. COUNT(dispID)
is stored into cnt and that variable is then used as the parameter to LIMIT
. This is the exception to the above-mentioned rule.
DELIMITER //
CREATE PROCEDURE select_counter()
BEGIN
DECLARE cnt INT;
SELECT COUNT(dispID) INTO cnt FROM dispanser;
SELECT * FROM counter LIMIT cnt;
END //
DELIMITER ;
DBFiddle
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
when i ranCALL select_counter()
i got this messageCurrent selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
|
show 1 more comment
up vote
0
down vote
Based on conversations in the comments, it sounds like what you're trying to do is get the count from counter
for each record in dispanser
- if this is wrong, please comment, and I can adjust my response. The best way to accomplish what you're looking for is through joining a subquery with the GROUP BY
syntax. Something like this might could work, depending on your schema:
SELECT
d.*,
c.total
FROM
dispanser as d
INNER JOIN (
SELECT
COUNT(*) as 'total',
dispID
FROM
counter
GROUP BY
dispID
) as c
ON c.dispID = d.id
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
Does your app create a new row incounter
every time a record is added todispanser
? Also, do yourcounter
anddispanser
tables have an auto-increment primary key (i.e. an ID column)?
– Jpec07
Nov 10 at 0:12
actually no....
– Aly Al Ameen
Nov 10 at 0:14
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in yourWHERE
statement, likeWHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using theHAVING
syntax instead, asHAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.
– Jpec07
Nov 10 at 0:18
can't i simply make somthing similar to this:select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
|
show 2 more comments
up vote
0
down vote
You can try to use dynamic SQL.
Set a variable @num
to get an amount from dispanser
table.
prepared your SQL statement CONCAT('select * from counter limit ', @num )
.
Final use EXECUTE
to execute SQL dynamically.
SET @sql = CONCAT('select * from counter order by counterID desc limit ', (SELECT count(dispID) from dispanser));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sqlfiddle
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
What's your scenario? BTW if your are usinglimit
it should sed withorder by
because you need to make sure the order.
– D-Shih
Nov 10 at 0:29
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is thisselect * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
|
show 5 more comments
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
You can't do this without using prepared statements or a stored procedure. From the manual:
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants
In a stored procedure you could do something like this. COUNT(dispID)
is stored into cnt and that variable is then used as the parameter to LIMIT
. This is the exception to the above-mentioned rule.
DELIMITER //
CREATE PROCEDURE select_counter()
BEGIN
DECLARE cnt INT;
SELECT COUNT(dispID) INTO cnt FROM dispanser;
SELECT * FROM counter LIMIT cnt;
END //
DELIMITER ;
DBFiddle
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
when i ranCALL select_counter()
i got this messageCurrent selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
|
show 1 more comment
up vote
0
down vote
accepted
You can't do this without using prepared statements or a stored procedure. From the manual:
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants
In a stored procedure you could do something like this. COUNT(dispID)
is stored into cnt and that variable is then used as the parameter to LIMIT
. This is the exception to the above-mentioned rule.
DELIMITER //
CREATE PROCEDURE select_counter()
BEGIN
DECLARE cnt INT;
SELECT COUNT(dispID) INTO cnt FROM dispanser;
SELECT * FROM counter LIMIT cnt;
END //
DELIMITER ;
DBFiddle
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
when i ranCALL select_counter()
i got this messageCurrent selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
|
show 1 more comment
up vote
0
down vote
accepted
up vote
0
down vote
accepted
You can't do this without using prepared statements or a stored procedure. From the manual:
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants
In a stored procedure you could do something like this. COUNT(dispID)
is stored into cnt and that variable is then used as the parameter to LIMIT
. This is the exception to the above-mentioned rule.
DELIMITER //
CREATE PROCEDURE select_counter()
BEGIN
DECLARE cnt INT;
SELECT COUNT(dispID) INTO cnt FROM dispanser;
SELECT * FROM counter LIMIT cnt;
END //
DELIMITER ;
DBFiddle
You can't do this without using prepared statements or a stored procedure. From the manual:
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants
In a stored procedure you could do something like this. COUNT(dispID)
is stored into cnt and that variable is then used as the parameter to LIMIT
. This is the exception to the above-mentioned rule.
DELIMITER //
CREATE PROCEDURE select_counter()
BEGIN
DECLARE cnt INT;
SELECT COUNT(dispID) INTO cnt FROM dispanser;
SELECT * FROM counter LIMIT cnt;
END //
DELIMITER ;
DBFiddle
edited Nov 9 at 23:56
answered Nov 9 at 23:51
Nick
20.6k51434
20.6k51434
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
when i ranCALL select_counter()
i got this messageCurrent selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
|
show 1 more comment
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
when i ranCALL select_counter()
i got this messageCurrent selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
Depending on data structure, it might be possible to work around that through some creative join syntax and subqueries.
– Jpec07
Nov 9 at 23:53
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
@Jpec07 true but this will probably be much faster...
– Nick
Nov 9 at 23:57
when i ran
CALL select_counter()
i got this message Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
when i ran
CALL select_counter()
i got this message Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
– Aly Al Ameen
Nov 10 at 0:55
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
@AlyAlAmeen that is just a PHPMyAdmin information message. It is not an error. It just means that you don't get the Edit Copy and Delete buttons next to each entry in the output.
– Nick
Nov 10 at 1:09
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
yes, the problem is that no result come when i call the function
– Aly Al Ameen
Nov 10 at 1:16
|
show 1 more comment
up vote
0
down vote
Based on conversations in the comments, it sounds like what you're trying to do is get the count from counter
for each record in dispanser
- if this is wrong, please comment, and I can adjust my response. The best way to accomplish what you're looking for is through joining a subquery with the GROUP BY
syntax. Something like this might could work, depending on your schema:
SELECT
d.*,
c.total
FROM
dispanser as d
INNER JOIN (
SELECT
COUNT(*) as 'total',
dispID
FROM
counter
GROUP BY
dispID
) as c
ON c.dispID = d.id
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
Does your app create a new row incounter
every time a record is added todispanser
? Also, do yourcounter
anddispanser
tables have an auto-increment primary key (i.e. an ID column)?
– Jpec07
Nov 10 at 0:12
actually no....
– Aly Al Ameen
Nov 10 at 0:14
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in yourWHERE
statement, likeWHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using theHAVING
syntax instead, asHAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.
– Jpec07
Nov 10 at 0:18
can't i simply make somthing similar to this:select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
|
show 2 more comments
up vote
0
down vote
Based on conversations in the comments, it sounds like what you're trying to do is get the count from counter
for each record in dispanser
- if this is wrong, please comment, and I can adjust my response. The best way to accomplish what you're looking for is through joining a subquery with the GROUP BY
syntax. Something like this might could work, depending on your schema:
SELECT
d.*,
c.total
FROM
dispanser as d
INNER JOIN (
SELECT
COUNT(*) as 'total',
dispID
FROM
counter
GROUP BY
dispID
) as c
ON c.dispID = d.id
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
Does your app create a new row incounter
every time a record is added todispanser
? Also, do yourcounter
anddispanser
tables have an auto-increment primary key (i.e. an ID column)?
– Jpec07
Nov 10 at 0:12
actually no....
– Aly Al Ameen
Nov 10 at 0:14
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in yourWHERE
statement, likeWHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using theHAVING
syntax instead, asHAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.
– Jpec07
Nov 10 at 0:18
can't i simply make somthing similar to this:select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
|
show 2 more comments
up vote
0
down vote
up vote
0
down vote
Based on conversations in the comments, it sounds like what you're trying to do is get the count from counter
for each record in dispanser
- if this is wrong, please comment, and I can adjust my response. The best way to accomplish what you're looking for is through joining a subquery with the GROUP BY
syntax. Something like this might could work, depending on your schema:
SELECT
d.*,
c.total
FROM
dispanser as d
INNER JOIN (
SELECT
COUNT(*) as 'total',
dispID
FROM
counter
GROUP BY
dispID
) as c
ON c.dispID = d.id
Based on conversations in the comments, it sounds like what you're trying to do is get the count from counter
for each record in dispanser
- if this is wrong, please comment, and I can adjust my response. The best way to accomplish what you're looking for is through joining a subquery with the GROUP BY
syntax. Something like this might could work, depending on your schema:
SELECT
d.*,
c.total
FROM
dispanser as d
INNER JOIN (
SELECT
COUNT(*) as 'total',
dispID
FROM
counter
GROUP BY
dispID
) as c
ON c.dispID = d.id
answered Nov 10 at 0:03
Jpec07
3136
3136
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
Does your app create a new row incounter
every time a record is added todispanser
? Also, do yourcounter
anddispanser
tables have an auto-increment primary key (i.e. an ID column)?
– Jpec07
Nov 10 at 0:12
actually no....
– Aly Al Ameen
Nov 10 at 0:14
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in yourWHERE
statement, likeWHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using theHAVING
syntax instead, asHAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.
– Jpec07
Nov 10 at 0:18
can't i simply make somthing similar to this:select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
|
show 2 more comments
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
Does your app create a new row incounter
every time a record is added todispanser
? Also, do yourcounter
anddispanser
tables have an auto-increment primary key (i.e. an ID column)?
– Jpec07
Nov 10 at 0:12
actually no....
– Aly Al Ameen
Nov 10 at 0:14
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in yourWHERE
statement, likeWHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using theHAVING
syntax instead, asHAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.
– Jpec07
Nov 10 at 0:18
can't i simply make somthing similar to this:select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
what i exactly want is to get the last records in table counter depends on the number of dispansers, and since user can add dispanser, i can't set fixed limit, so i have to count the dispansers and than use it to limit the selection ..
– Aly Al Ameen
Nov 10 at 0:10
Does your app create a new row in
counter
every time a record is added to dispanser
? Also, do your counter
and dispanser
tables have an auto-increment primary key (i.e. an ID column)?– Jpec07
Nov 10 at 0:12
Does your app create a new row in
counter
every time a record is added to dispanser
? Also, do your counter
and dispanser
tables have an auto-increment primary key (i.e. an ID column)?– Jpec07
Nov 10 at 0:12
actually no....
– Aly Al Ameen
Nov 10 at 0:14
actually no....
– Aly Al Ameen
Nov 10 at 0:14
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in your
WHERE
statement, like WHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using the HAVING
syntax instead, as HAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.– Jpec07
Nov 10 at 0:18
If you have access to the schema to be able to modify the tables, I'd add such a thing. You can then reference that in a subquery in your
WHERE
statement, like WHERE counter.id = (SELECT MAX(id) FROM counter)
- or possibly even using the HAVING
syntax instead, as HAVING counter.id = MAX(counter.id)
. It's really handy for things like this, and is good to consider when you model your data going forward.– Jpec07
Nov 10 at 0:18
can't i simply make somthing similar to this:
select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
can't i simply make somthing similar to this:
select * from counter order by counterID desc limit (select count(dispID) from counter)
– Aly Al Ameen
Nov 10 at 0:24
|
show 2 more comments
up vote
0
down vote
You can try to use dynamic SQL.
Set a variable @num
to get an amount from dispanser
table.
prepared your SQL statement CONCAT('select * from counter limit ', @num )
.
Final use EXECUTE
to execute SQL dynamically.
SET @sql = CONCAT('select * from counter order by counterID desc limit ', (SELECT count(dispID) from dispanser));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sqlfiddle
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
What's your scenario? BTW if your are usinglimit
it should sed withorder by
because you need to make sure the order.
– D-Shih
Nov 10 at 0:29
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is thisselect * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
|
show 5 more comments
up vote
0
down vote
You can try to use dynamic SQL.
Set a variable @num
to get an amount from dispanser
table.
prepared your SQL statement CONCAT('select * from counter limit ', @num )
.
Final use EXECUTE
to execute SQL dynamically.
SET @sql = CONCAT('select * from counter order by counterID desc limit ', (SELECT count(dispID) from dispanser));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sqlfiddle
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
What's your scenario? BTW if your are usinglimit
it should sed withorder by
because you need to make sure the order.
– D-Shih
Nov 10 at 0:29
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is thisselect * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
|
show 5 more comments
up vote
0
down vote
up vote
0
down vote
You can try to use dynamic SQL.
Set a variable @num
to get an amount from dispanser
table.
prepared your SQL statement CONCAT('select * from counter limit ', @num )
.
Final use EXECUTE
to execute SQL dynamically.
SET @sql = CONCAT('select * from counter order by counterID desc limit ', (SELECT count(dispID) from dispanser));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sqlfiddle
You can try to use dynamic SQL.
Set a variable @num
to get an amount from dispanser
table.
prepared your SQL statement CONCAT('select * from counter limit ', @num )
.
Final use EXECUTE
to execute SQL dynamically.
SET @sql = CONCAT('select * from counter order by counterID desc limit ', (SELECT count(dispID) from dispanser));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sqlfiddle
edited Nov 10 at 1:23
answered Nov 9 at 23:51
D-Shih
24.4k61431
24.4k61431
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
What's your scenario? BTW if your are usinglimit
it should sed withorder by
because you need to make sure the order.
– D-Shih
Nov 10 at 0:29
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is thisselect * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
|
show 5 more comments
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
What's your scenario? BTW if your are usinglimit
it should sed withorder by
because you need to make sure the order.
– D-Shih
Nov 10 at 0:29
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is thisselect * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
i saw the fiddle, it works , but when i apply it to my scenario i don't get desired result
– Aly Al Ameen
Nov 10 at 0:26
What's your scenario? BTW if your are using
limit
it should sed with order by
because you need to make sure the order.– D-Shih
Nov 10 at 0:29
What's your scenario? BTW if your are using
limit
it should sed with order by
because you need to make sure the order.– D-Shih
Nov 10 at 0:29
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
Or could you provide some sample data and expect result?
– D-Shih
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
okay i will set the scenario
– Aly Al Ameen
Nov 10 at 0:31
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is this
select * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
it is really so hard to explain the scenario since i am taking about gasoline station, but finally i have two tables as you set in the fiddle, but more columns, but what i want exactly is this
select * from counter order by counterID desc limit (select count(dispID) from dispanser)
– Aly Al Ameen
Nov 10 at 0:38
|
show 5 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53234654%2fhow-to-set-the-value-of-limit-using-select-countid-from-another-table-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I got no idea, but found your question interesting. Why would you like to do that?
– luisfer
Nov 9 at 23:46
Is there any relationship between the data in the
counter
anddispanser
tables - i.e. doesdispanser
have acounterId
field or something similar?– Jpec07
Nov 9 at 23:51
@luisfer because i want the last records in table counter, and the
last
is a dynamic value so, i need each time to count– Aly Al Ameen
Nov 9 at 23:55
@Jpec07 yes
counter
table hasdispID
– Aly Al Ameen
Nov 9 at 23:55
Do you have access to MySQL version >= 8.0.2 ?
– Madhur Bhaiya
Nov 10 at 9:11