Select FIRST X from query, but get count (for example for paging)
0
I want to return the first x rows, but also need the total number of items in my select. In MSSQL I figured it out, but I didn't find a solution to the the same in Interbase.
Does such statement exist also for Firebird?
And one general question, is such embedded function (also in MSSQL) actually faster than to execute a second statement in order to get the total rowcount?
sql firebird
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
asked Jan 19 at 18:39
rimesrimes
1881213
add a comment |
0
I want to return the first x rows, but also need the total number of items in my select. In MSSQL I figured it out, but I didn't find a solution to the the same in Interbase.
Does such statement exist also for Firebird?
And one general question, is such embedded function (also in MSSQL) actually faster than to execute a second statement in order to get the total rowcount?
sql firebird
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
asked Jan 19 at 18:39
rimesrimes
1881213
how do you do in MSSQL?
– dani herrera
Jan 19 at 19:07
Are you using Firebird or InterBase? They are not the same since Firebird was forked 19 years ago. If Firebird, what version of Firebird (eg in Firebird 3 it is a lot easier than in earlier versions)? Also as Dani asked, show how you solved it in Microsoft SQL Server.
– Mark Rotteveel
Jan 20 at 8:56
In SQL its pretty easy. Just by adding this part to the SELECT (when selecting the rows) "numberofrows= COUNT(myrow) OVER()".Actually it would be perfect if such statement exists for interbase and firebird (since I have both databases in use), but for the beginning just firebird would also be fine
– rimes
Jan 20 at 9:08
You should edit your question with that information, in any case Firebird 3 supportsCOUNT(myrow) OVER()
– Mark Rotteveel
Jan 20 at 11:52
In any case, I have removed the InterBase tag, because having to take into account two different database systems will make this too broad to answer (even if they share the same origin). If you also need to know for recent InterBase versions, you should ask a separate question.
– Mark Rotteveel
Jan 20 at 12:06
add a comment |
0
0
0
I want to return the first x rows, but also need the total number of items in my select. In MSSQL I figured it out, but I didn't find a solution to the the same in Interbase.
Does such statement exist also for Firebird?
And one general question, is such embedded function (also in MSSQL) actually faster than to execute a second statement in order to get the total rowcount?
sql firebird
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
asked Jan 19 at 18:39
rimesrimes
1881213
I want to return the first x rows, but also need the total number of items in my select. In MSSQL I figured it out, but I didn't find a solution to the the same in Interbase.
Does such statement exist also for Firebird?
And one general question, is such embedded function (also in MSSQL) actually faster than to execute a second statement in order to get the total rowcount?
sql firebird
sql firebird
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
asked Jan 19 at 18:39
rimesrimes
1881213
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
asked Jan 19 at 18:39
rimesrimes
1881213
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
edited Jan 20 at 12:05
Mark Rotteveel
60.3k1477121
60.3k1477121
asked Jan 19 at 18:39
rimesrimes
1881213
asked Jan 19 at 18:39
rimesrimes
1881213
asked Jan 19 at 18:39
rimesrimes
1881213
1881213
how do you do in MSSQL?
– dani herrera
Jan 19 at 19:07
Are you using Firebird or InterBase? They are not the same since Firebird was forked 19 years ago. If Firebird, what version of Firebird (eg in Firebird 3 it is a lot easier than in earlier versions)? Also as Dani asked, show how you solved it in Microsoft SQL Server.
– Mark Rotteveel
Jan 20 at 8:56
In SQL its pretty easy. Just by adding this part to the SELECT (when selecting the rows) "numberofrows= COUNT(myrow) OVER()".Actually it would be perfect if such statement exists for interbase and firebird (since I have both databases in use), but for the beginning just firebird would also be fine
– rimes
Jan 20 at 9:08
You should edit your question with that information, in any case Firebird 3 supportsCOUNT(myrow) OVER()
– Mark Rotteveel
Jan 20 at 11:52
In any case, I have removed the InterBase tag, because having to take into account two different database systems will make this too broad to answer (even if they share the same origin). If you also need to know for recent InterBase versions, you should ask a separate question.
– Mark Rotteveel
Jan 20 at 12:06
add a comment |
how do you do in MSSQL?
– dani herrera
Jan 19 at 19:07
Are you using Firebird or InterBase? They are not the same since Firebird was forked 19 years ago. If Firebird, what version of Firebird (eg in Firebird 3 it is a lot easier than in earlier versions)? Also as Dani asked, show how you solved it in Microsoft SQL Server.
– Mark Rotteveel
Jan 20 at 8:56
In SQL its pretty easy. Just by adding this part to the SELECT (when selecting the rows) "numberofrows= COUNT(myrow) OVER()".Actually it would be perfect if such statement exists for interbase and firebird (since I have both databases in use), but for the beginning just firebird would also be fine
– rimes
Jan 20 at 9:08
You should edit your question with that information, in any case Firebird 3 supportsCOUNT(myrow) OVER()
– Mark Rotteveel
Jan 20 at 11:52
In any case, I have removed the InterBase tag, because having to take into account two different database systems will make this too broad to answer (even if they share the same origin). If you also need to know for recent InterBase versions, you should ask a separate question.
– Mark Rotteveel
Jan 20 at 12:06
how do you do in MSSQL?
– dani herrera
Jan 19 at 19:07
how do you do in MSSQL?
– dani herrera
Jan 19 at 19:07
Are you using Firebird or InterBase? They are not the same since Firebird was forked 19 years ago. If Firebird, what version of Firebird (eg in Firebird 3 it is a lot easier than in earlier versions)? Also as Dani asked, show how you solved it in Microsoft SQL Server.
– Mark Rotteveel
Jan 20 at 8:56
Are you using Firebird or InterBase? They are not the same since Firebird was forked 19 years ago. If Firebird, what version of Firebird (eg in Firebird 3 it is a lot easier than in earlier versions)? Also as Dani asked, show how you solved it in Microsoft SQL Server.
– Mark Rotteveel
Jan 20 at 8:56
In SQL its pretty easy. Just by adding this part to the SELECT (when selecting the rows) "numberofrows= COUNT(myrow) OVER()".Actually it would be perfect if such statement exists for interbase and firebird (since I have both databases in use), but for the beginning just firebird would also be fine
– rimes
Jan 20 at 9:08
In SQL its pretty easy. Just by adding this part to the SELECT (when selecting the rows) "numberofrows= COUNT(myrow) OVER()".Actually it would be perfect if such statement exists for interbase and firebird (since I have both databases in use), but for the beginning just firebird would also be fine
– rimes
Jan 20 at 9:08
You should edit your question with that information, in any case Firebird 3 supports
COUNT(myrow) OVER()– Mark Rotteveel
Jan 20 at 11:52
You should edit your question with that information, in any case Firebird 3 supports
COUNT(myrow) OVER()– Mark Rotteveel
Jan 20 at 11:52
In any case, I have removed the InterBase tag, because having to take into account two different database systems will make this too broad to answer (even if they share the same origin). If you also need to know for recent InterBase versions, you should ask a separate question.
– Mark Rotteveel
Jan 20 at 12:06
In any case, I have removed the InterBase tag, because having to take into account two different database systems will make this too broad to answer (even if they share the same origin). If you also need to know for recent InterBase versions, you should ask a separate question.
– Mark Rotteveel
Jan 20 at 12:06
add a comment |
2 Answers
2
active
oldest
votes
0
If you are using Firebird 3, you can use window functions:
select count(*) over(), column1, column2, etc
from sometable
where somecondition
order by something
fetch first 10 rows only
This uses the fetch clause introduced in Firebird 3.
In earlier versions doing this is a bit harder, but for example in Firebird 2.1 and higher, you could use a common table expression to prevent having to repeat the query and conditions:
with actualquery as (
select column1, column2, etc
from sometable
where somecondition
)
select (select count(*) from actualquery), column1, column2, etc
from actualquery
order by something
rows 10
This uses the rows clause (which is similar to select first 10)
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
1
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
1
@rimes, just think twice usingwindowfunctions, not applicable in this context. A query likeselect count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows onlyon a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.
– Marcodor
Jan 20 at 15:15
|
show 6 more comments
2
You need two separate requests.
One is to receive limited rows set using SQL compliant offset & fetch starting with Firebird 3 or alternatively rows clause or first & skip:
select field1, field2
from mytable
[where conditions]
[offset 0 rows] fetch first 10 rows only
Another one is to receive total record count:
select count(*) from mytable [where conditions]
Note, last query may be costly on complex queries and/or big amount data. Usually on large amount of data another approaches are used. One is dedicated FTS engines, like SphinxSearch.
Google for example give you an approximate results count.
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
1
Contrary to what some parts of the Firebird documentation claim,rowsis not a SQL compliant clause. The SQL standard clauses are theoffset/fetchclauses introduced in Firebird 3.
– Mark Rotteveel
Jan 20 at 11:51
Strange, even FB core devs argues thatrowscome from the standard when they implemented it along with already existedfirst. But, indeed, from FB3,fetchwill be more dbms agnostic to use.
– Marcodor
Jan 20 at 12:13
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any caserowsitself has never been in a finalized published SQL standard.
– Mark Rotteveel
Jan 20 at 12:20
add a comment |
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',
autoActivateHeartbeat: false,
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
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%2f54270212%2fselect-first-x-from-query-but-get-count-for-example-for-paging%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
If you are using Firebird 3, you can use window functions:
select count(*) over(), column1, column2, etc
from sometable
where somecondition
order by something
fetch first 10 rows only
This uses the fetch clause introduced in Firebird 3.
In earlier versions doing this is a bit harder, but for example in Firebird 2.1 and higher, you could use a common table expression to prevent having to repeat the query and conditions:
with actualquery as (
select column1, column2, etc
from sometable
where somecondition
)
select (select count(*) from actualquery), column1, column2, etc
from actualquery
order by something
rows 10
This uses the rows clause (which is similar to select first 10)
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
1
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
1
@rimes, just think twice usingwindowfunctions, not applicable in this context. A query likeselect count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows onlyon a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.
– Marcodor
Jan 20 at 15:15
|
show 6 more comments
0
If you are using Firebird 3, you can use window functions:
select count(*) over(), column1, column2, etc
from sometable
where somecondition
order by something
fetch first 10 rows only
This uses the fetch clause introduced in Firebird 3.
In earlier versions doing this is a bit harder, but for example in Firebird 2.1 and higher, you could use a common table expression to prevent having to repeat the query and conditions:
with actualquery as (
select column1, column2, etc
from sometable
where somecondition
)
select (select count(*) from actualquery), column1, column2, etc
from actualquery
order by something
rows 10
This uses the rows clause (which is similar to select first 10)
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
1
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
1
@rimes, just think twice usingwindowfunctions, not applicable in this context. A query likeselect count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows onlyon a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.
– Marcodor
Jan 20 at 15:15
|
show 6 more comments
0
0
0
If you are using Firebird 3, you can use window functions:
select count(*) over(), column1, column2, etc
from sometable
where somecondition
order by something
fetch first 10 rows only
This uses the fetch clause introduced in Firebird 3.
In earlier versions doing this is a bit harder, but for example in Firebird 2.1 and higher, you could use a common table expression to prevent having to repeat the query and conditions:
with actualquery as (
select column1, column2, etc
from sometable
where somecondition
)
select (select count(*) from actualquery), column1, column2, etc
from actualquery
order by something
rows 10
This uses the rows clause (which is similar to select first 10)
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
If you are using Firebird 3, you can use window functions:
select count(*) over(), column1, column2, etc
from sometable
where somecondition
order by something
fetch first 10 rows only
This uses the fetch clause introduced in Firebird 3.
In earlier versions doing this is a bit harder, but for example in Firebird 2.1 and higher, you could use a common table expression to prevent having to repeat the query and conditions:
with actualquery as (
select column1, column2, etc
from sometable
where somecondition
)
select (select count(*) from actualquery), column1, column2, etc
from actualquery
order by something
rows 10
This uses the rows clause (which is similar to select first 10)
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
edited Jan 20 at 12:14
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
answered Jan 20 at 12:01
Mark RotteveelMark Rotteveel
60.3k1477121
60.3k1477121
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
1
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
1
@rimes, just think twice usingwindowfunctions, not applicable in this context. A query likeselect count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows onlyon a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.
– Marcodor
Jan 20 at 15:15
|
show 6 more comments
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
1
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
1
@rimes, just think twice usingwindowfunctions, not applicable in this context. A query likeselect count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows onlyon a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.
– Marcodor
Jan 20 at 15:15
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Nice solution, although it does not make much sense to fetch count value multiple times at each row. Later you need to "separate" it or take it into account during data/fields iteration/management. But if you want to get count and data in one shot, this is the way.
– Marcodor
Jan 20 at 12:47
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
Thank you for the answer, thats what I was looking for. But to the other part of the question, do you know if its faster to execute the rowcount with the select statement, or to run one additional query in order to get the results (or is it dependent on the complexity of the query itself)
– rimes
Jan 20 at 12:51
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
@Marcodor the question is if the rowcount select is internally somehow optimized and not fetched for every row
– rimes
Jan 20 at 12:53
1
1
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
Just did a test, unfortunately FB3 optimizer is not so smart enough (yet), and evaluate count for each row.
– Marcodor
Jan 20 at 15:00
1
1
@rimes, just think twice using
window functions, not applicable in this context. A query like select count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows only on a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.– Marcodor
Jan 20 at 15:15
@rimes, just think twice using
window functions, not applicable in this context. A query like select count(*) over(), T.ID, T.NAME from TA_TEST T offset 100000 rows fetch first 20 rows only on a table with 650k records is executed in 5.4s. Two requests runs in 60ms (data) and count(320ms) resulting in near 0.4s.– Marcodor
Jan 20 at 15:15
|
show 6 more comments
2
You need two separate requests.
One is to receive limited rows set using SQL compliant offset & fetch starting with Firebird 3 or alternatively rows clause or first & skip:
select field1, field2
from mytable
[where conditions]
[offset 0 rows] fetch first 10 rows only
Another one is to receive total record count:
select count(*) from mytable [where conditions]
Note, last query may be costly on complex queries and/or big amount data. Usually on large amount of data another approaches are used. One is dedicated FTS engines, like SphinxSearch.
Google for example give you an approximate results count.
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
1
Contrary to what some parts of the Firebird documentation claim,rowsis not a SQL compliant clause. The SQL standard clauses are theoffset/fetchclauses introduced in Firebird 3.
– Mark Rotteveel
Jan 20 at 11:51
Strange, even FB core devs argues thatrowscome from the standard when they implemented it along with already existedfirst. But, indeed, from FB3,fetchwill be more dbms agnostic to use.
– Marcodor
Jan 20 at 12:13
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any caserowsitself has never been in a finalized published SQL standard.
– Mark Rotteveel
Jan 20 at 12:20
add a comment |
2
You need two separate requests.
One is to receive limited rows set using SQL compliant offset & fetch starting with Firebird 3 or alternatively rows clause or first & skip:
select field1, field2
from mytable
[where conditions]
[offset 0 rows] fetch first 10 rows only
Another one is to receive total record count:
select count(*) from mytable [where conditions]
Note, last query may be costly on complex queries and/or big amount data. Usually on large amount of data another approaches are used. One is dedicated FTS engines, like SphinxSearch.
Google for example give you an approximate results count.
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
1
Contrary to what some parts of the Firebird documentation claim,rowsis not a SQL compliant clause. The SQL standard clauses are theoffset/fetchclauses introduced in Firebird 3.
– Mark Rotteveel
Jan 20 at 11:51
Strange, even FB core devs argues thatrowscome from the standard when they implemented it along with already existedfirst. But, indeed, from FB3,fetchwill be more dbms agnostic to use.
– Marcodor
Jan 20 at 12:13
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any caserowsitself has never been in a finalized published SQL standard.
– Mark Rotteveel
Jan 20 at 12:20
add a comment |
2
2
2
You need two separate requests.
One is to receive limited rows set using SQL compliant offset & fetch starting with Firebird 3 or alternatively rows clause or first & skip:
select field1, field2
from mytable
[where conditions]
[offset 0 rows] fetch first 10 rows only
Another one is to receive total record count:
select count(*) from mytable [where conditions]
Note, last query may be costly on complex queries and/or big amount data. Usually on large amount of data another approaches are used. One is dedicated FTS engines, like SphinxSearch.
Google for example give you an approximate results count.
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
You need two separate requests.
One is to receive limited rows set using SQL compliant offset & fetch starting with Firebird 3 or alternatively rows clause or first & skip:
select field1, field2
from mytable
[where conditions]
[offset 0 rows] fetch first 10 rows only
Another one is to receive total record count:
select count(*) from mytable [where conditions]
Note, last query may be costly on complex queries and/or big amount data. Usually on large amount of data another approaches are used. One is dedicated FTS engines, like SphinxSearch.
Google for example give you an approximate results count.
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
edited Jan 20 at 15:07
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
answered Jan 20 at 10:40
MarcodorMarcodor
1,379919
1,379919
1
Contrary to what some parts of the Firebird documentation claim,rowsis not a SQL compliant clause. The SQL standard clauses are theoffset/fetchclauses introduced in Firebird 3.
– Mark Rotteveel
Jan 20 at 11:51
Strange, even FB core devs argues thatrowscome from the standard when they implemented it along with already existedfirst. But, indeed, from FB3,fetchwill be more dbms agnostic to use.
– Marcodor
Jan 20 at 12:13
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any caserowsitself has never been in a finalized published SQL standard.
– Mark Rotteveel
Jan 20 at 12:20
add a comment |
1
Contrary to what some parts of the Firebird documentation claim,rowsis not a SQL compliant clause. The SQL standard clauses are theoffset/fetchclauses introduced in Firebird 3.
– Mark Rotteveel
Jan 20 at 11:51
Strange, even FB core devs argues thatrowscome from the standard when they implemented it along with already existedfirst. But, indeed, from FB3,fetchwill be more dbms agnostic to use.
– Marcodor
Jan 20 at 12:13
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any caserowsitself has never been in a finalized published SQL standard.
– Mark Rotteveel
Jan 20 at 12:20
1
1
Contrary to what some parts of the Firebird documentation claim,
rows is not a SQL compliant clause. The SQL standard clauses are the offset/fetch clauses introduced in Firebird 3.– Mark Rotteveel
Jan 20 at 11:51
Contrary to what some parts of the Firebird documentation claim,
rows is not a SQL compliant clause. The SQL standard clauses are the offset/fetch clauses introduced in Firebird 3.– Mark Rotteveel
Jan 20 at 11:51
Strange, even FB core devs argues that
rows come from the standard when they implemented it along with already existed first. But, indeed, from FB3, fetch will be more dbms agnostic to use.– Marcodor
Jan 20 at 12:13
Strange, even FB core devs argues that
rows come from the standard when they implemented it along with already existed first. But, indeed, from FB3, fetch will be more dbms agnostic to use.– Marcodor
Jan 20 at 12:13
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any case
rows itself has never been in a finalized published SQL standard.– Mark Rotteveel
Jan 20 at 12:20
I'm not sure why they thought it was defined in the SQL standard, maybe it was based on an early draft of a SQL standard. In any case
rows itself has never been in a finalized published SQL standard.– Mark Rotteveel
Jan 20 at 12:20
add a comment |
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.
draft saved
draft discarded
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%2f54270212%2fselect-first-x-from-query-but-get-count-for-example-for-paging%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
how do you do in MSSQL?
– dani herrera
Jan 19 at 19:07
Are you using Firebird or InterBase? They are not the same since Firebird was forked 19 years ago. If Firebird, what version of Firebird (eg in Firebird 3 it is a lot easier than in earlier versions)? Also as Dani asked, show how you solved it in Microsoft SQL Server.
– Mark Rotteveel
Jan 20 at 8:56
In SQL its pretty easy. Just by adding this part to the SELECT (when selecting the rows) "numberofrows= COUNT(myrow) OVER()".Actually it would be perfect if such statement exists for interbase and firebird (since I have both databases in use), but for the beginning just firebird would also be fine
– rimes
Jan 20 at 9:08
You should edit your question with that information, in any case Firebird 3 supports
COUNT(myrow) OVER()– Mark Rotteveel
Jan 20 at 11:52
In any case, I have removed the InterBase tag, because having to take into account two different database systems will make this too broad to answer (even if they share the same origin). If you also need to know for recent InterBase versions, you should ask a separate question.
– Mark Rotteveel
Jan 20 at 12:06