how to set the value of LIMIT using select count(id) from another table |MySQL











up vote
1
down vote

favorite
1












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) 









share|improve this question






















  • 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












  • @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










  • Do you have access to MySQL version >= 8.0.2 ?
    – Madhur Bhaiya
    Nov 10 at 9:11















up vote
1
down vote

favorite
1












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) 









share|improve this question






















  • 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












  • @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










  • Do you have access to MySQL version >= 8.0.2 ?
    – Madhur Bhaiya
    Nov 10 at 9:11













up vote
1
down vote

favorite
1









up vote
1
down vote

favorite
1






1





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) 









share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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 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










  • @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


















  • 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












  • @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










  • 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












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






share|improve this answer























  • 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 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










  • yes, the problem is that no result come when i call the function
    – Aly Al Ameen
    Nov 10 at 1:16


















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





share|improve this answer





















  • 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












  • 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










  • 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




















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






share|improve this answer























  • 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












  • 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 this select * from counter order by counterID desc limit (select count(dispID) from dispanser)
    – Aly Al Ameen
    Nov 10 at 0:38













Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















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

























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






share|improve this answer























  • 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 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










  • yes, the problem is that no result come when i call the function
    – Aly Al Ameen
    Nov 10 at 1:16















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






share|improve this answer























  • 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 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










  • yes, the problem is that no result come when i call the function
    – Aly Al Ameen
    Nov 10 at 1:16













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






share|improve this answer














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







share|improve this answer














share|improve this answer



share|improve this answer








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 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










  • 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










  • @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










  • @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












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





share|improve this answer





















  • 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












  • 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










  • 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

















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





share|improve this answer





















  • 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












  • 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










  • 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















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





share|improve this answer












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






share|improve this answer












share|improve this answer



share|improve this answer










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 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










  • 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




















  • 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












  • 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










  • 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












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






share|improve this answer























  • 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












  • 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 this select * from counter order by counterID desc limit (select count(dispID) from dispanser)
    – Aly Al Ameen
    Nov 10 at 0:38

















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






share|improve this answer























  • 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












  • 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 this select * from counter order by counterID desc limit (select count(dispID) from dispanser)
    – Aly Al Ameen
    Nov 10 at 0:38















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






share|improve this answer














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







share|improve this answer














share|improve this answer



share|improve this answer








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 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










  • 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




















  • 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












  • 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 this select * 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

Schultheiß

Verwaltungsgliederung Dänemarks

Liste der Kulturdenkmale in Wilsdruff