ONLY keyword - meaning
I have this simple case of query:
;WITH TEST_CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM TEST_CTE
WHERE N < 50
)
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
What does keyword ONLY
mean here:
FETCH NEXT 5 ROWS ONLY
Why is it necessary to have it there?
sql-server tsql
add a comment |
I have this simple case of query:
;WITH TEST_CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM TEST_CTE
WHERE N < 50
)
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
What does keyword ONLY
mean here:
FETCH NEXT 5 ROWS ONLY
Why is it necessary to have it there?
sql-server tsql
1
technet.microsoft.com/en-us/library/…
– Mitch Wheat
Nov 13 '18 at 6:15
Thanks, I've seen this - but keyword only is just used there. No word about it. Is anywhere explanation about this keyword solely?
– FrenkyB
Nov 13 '18 at 6:20
1
"Why is it necessary to have it there?" because that's what the authors of T-SQL decided. Why did they decide this? I don't know.
– Zohar Peled
Nov 13 '18 at 6:36
1
We can say like it's part of sql syntax for FETCH NEXT.
– NP007
Nov 13 '18 at 6:39
add a comment |
I have this simple case of query:
;WITH TEST_CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM TEST_CTE
WHERE N < 50
)
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
What does keyword ONLY
mean here:
FETCH NEXT 5 ROWS ONLY
Why is it necessary to have it there?
sql-server tsql
I have this simple case of query:
;WITH TEST_CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM TEST_CTE
WHERE N < 50
)
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
What does keyword ONLY
mean here:
FETCH NEXT 5 ROWS ONLY
Why is it necessary to have it there?
sql-server tsql
sql-server tsql
asked Nov 13 '18 at 6:14
FrenkyB
2,10453361
2,10453361
1
technet.microsoft.com/en-us/library/…
– Mitch Wheat
Nov 13 '18 at 6:15
Thanks, I've seen this - but keyword only is just used there. No word about it. Is anywhere explanation about this keyword solely?
– FrenkyB
Nov 13 '18 at 6:20
1
"Why is it necessary to have it there?" because that's what the authors of T-SQL decided. Why did they decide this? I don't know.
– Zohar Peled
Nov 13 '18 at 6:36
1
We can say like it's part of sql syntax for FETCH NEXT.
– NP007
Nov 13 '18 at 6:39
add a comment |
1
technet.microsoft.com/en-us/library/…
– Mitch Wheat
Nov 13 '18 at 6:15
Thanks, I've seen this - but keyword only is just used there. No word about it. Is anywhere explanation about this keyword solely?
– FrenkyB
Nov 13 '18 at 6:20
1
"Why is it necessary to have it there?" because that's what the authors of T-SQL decided. Why did they decide this? I don't know.
– Zohar Peled
Nov 13 '18 at 6:36
1
We can say like it's part of sql syntax for FETCH NEXT.
– NP007
Nov 13 '18 at 6:39
1
1
technet.microsoft.com/en-us/library/…
– Mitch Wheat
Nov 13 '18 at 6:15
technet.microsoft.com/en-us/library/…
– Mitch Wheat
Nov 13 '18 at 6:15
Thanks, I've seen this - but keyword only is just used there. No word about it. Is anywhere explanation about this keyword solely?
– FrenkyB
Nov 13 '18 at 6:20
Thanks, I've seen this - but keyword only is just used there. No word about it. Is anywhere explanation about this keyword solely?
– FrenkyB
Nov 13 '18 at 6:20
1
1
"Why is it necessary to have it there?" because that's what the authors of T-SQL decided. Why did they decide this? I don't know.
– Zohar Peled
Nov 13 '18 at 6:36
"Why is it necessary to have it there?" because that's what the authors of T-SQL decided. Why did they decide this? I don't know.
– Zohar Peled
Nov 13 '18 at 6:36
1
1
We can say like it's part of sql syntax for FETCH NEXT.
– NP007
Nov 13 '18 at 6:39
We can say like it's part of sql syntax for FETCH NEXT.
– NP007
Nov 13 '18 at 6:39
add a comment |
2 Answers
2
active
oldest
votes
If we check the syntax of the ORDER BY
clause when OFFSET
is used:
<offset_fetch> ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
You can see that if you need only to OFSET
rows, you do not need to specify how many rows to FETCH
(you are getting all of the rest). But if you want to get particular amount of rows, the ONLY
keyword is a must.
I try to find where exactly in the SQL standard
this is defined, because in many cases this is just the standard - it may look as something that is not needed, but this is the standard and people are trying to follow it. So, from here:
Since ISO SQL:2008 results limits can be specified as in the following
example using the FETCH FIRST clause.
SELECT * FROM T FETCH FIRST 10 ROWS ONLY
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP
SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c
and Mimer SQL.
Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is
considered part of the ORDER BY clause. The ORDER BY, OFFSET, and
FETCH FIRST clauses are all required for this usage.
SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10
ROWS ONLY
You can see, that the ONLY
keyword seems to be part of the standard definition. Below is a table where some implemented a syntax not following the standard:
So, in case of SQL Server
the only
keyword is a must when you are using ORDER BY
and FETCH
. This seems to be following the standard, so do not waste too much time to worry about it.
add a comment |
The OFFSET FETCH
is used for pagination purpose ie; to retrieve only some rows as specified
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
So here it won't return the first 10 rows when ordering by n because it is specified as the offset . Then it will return the remaining 5 rows only
ONLY
is used to specify the rows needed.
If it is not specified then the query will skip the first 10 rows from the sorted result set and return the remaining rows.
If you only want to offset the rows the use OFFSET
keyword only.
If you need to limit the number of rows returned then use FETCH
with ONLY
DEMO
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
@FrenkyB i have updated the answer and included a demo for you.ONLY
is a part of fetch but not part offset.
– Sanal Sunny
Nov 13 '18 at 6:34
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
});
}
});
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%2f53274875%2fonly-keyword-meaning%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
If we check the syntax of the ORDER BY
clause when OFFSET
is used:
<offset_fetch> ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
You can see that if you need only to OFSET
rows, you do not need to specify how many rows to FETCH
(you are getting all of the rest). But if you want to get particular amount of rows, the ONLY
keyword is a must.
I try to find where exactly in the SQL standard
this is defined, because in many cases this is just the standard - it may look as something that is not needed, but this is the standard and people are trying to follow it. So, from here:
Since ISO SQL:2008 results limits can be specified as in the following
example using the FETCH FIRST clause.
SELECT * FROM T FETCH FIRST 10 ROWS ONLY
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP
SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c
and Mimer SQL.
Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is
considered part of the ORDER BY clause. The ORDER BY, OFFSET, and
FETCH FIRST clauses are all required for this usage.
SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10
ROWS ONLY
You can see, that the ONLY
keyword seems to be part of the standard definition. Below is a table where some implemented a syntax not following the standard:
So, in case of SQL Server
the only
keyword is a must when you are using ORDER BY
and FETCH
. This seems to be following the standard, so do not waste too much time to worry about it.
add a comment |
If we check the syntax of the ORDER BY
clause when OFFSET
is used:
<offset_fetch> ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
You can see that if you need only to OFSET
rows, you do not need to specify how many rows to FETCH
(you are getting all of the rest). But if you want to get particular amount of rows, the ONLY
keyword is a must.
I try to find where exactly in the SQL standard
this is defined, because in many cases this is just the standard - it may look as something that is not needed, but this is the standard and people are trying to follow it. So, from here:
Since ISO SQL:2008 results limits can be specified as in the following
example using the FETCH FIRST clause.
SELECT * FROM T FETCH FIRST 10 ROWS ONLY
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP
SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c
and Mimer SQL.
Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is
considered part of the ORDER BY clause. The ORDER BY, OFFSET, and
FETCH FIRST clauses are all required for this usage.
SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10
ROWS ONLY
You can see, that the ONLY
keyword seems to be part of the standard definition. Below is a table where some implemented a syntax not following the standard:
So, in case of SQL Server
the only
keyword is a must when you are using ORDER BY
and FETCH
. This seems to be following the standard, so do not waste too much time to worry about it.
add a comment |
If we check the syntax of the ORDER BY
clause when OFFSET
is used:
<offset_fetch> ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
You can see that if you need only to OFSET
rows, you do not need to specify how many rows to FETCH
(you are getting all of the rest). But if you want to get particular amount of rows, the ONLY
keyword is a must.
I try to find where exactly in the SQL standard
this is defined, because in many cases this is just the standard - it may look as something that is not needed, but this is the standard and people are trying to follow it. So, from here:
Since ISO SQL:2008 results limits can be specified as in the following
example using the FETCH FIRST clause.
SELECT * FROM T FETCH FIRST 10 ROWS ONLY
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP
SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c
and Mimer SQL.
Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is
considered part of the ORDER BY clause. The ORDER BY, OFFSET, and
FETCH FIRST clauses are all required for this usage.
SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10
ROWS ONLY
You can see, that the ONLY
keyword seems to be part of the standard definition. Below is a table where some implemented a syntax not following the standard:
So, in case of SQL Server
the only
keyword is a must when you are using ORDER BY
and FETCH
. This seems to be following the standard, so do not waste too much time to worry about it.
If we check the syntax of the ORDER BY
clause when OFFSET
is used:
<offset_fetch> ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
You can see that if you need only to OFSET
rows, you do not need to specify how many rows to FETCH
(you are getting all of the rest). But if you want to get particular amount of rows, the ONLY
keyword is a must.
I try to find where exactly in the SQL standard
this is defined, because in many cases this is just the standard - it may look as something that is not needed, but this is the standard and people are trying to follow it. So, from here:
Since ISO SQL:2008 results limits can be specified as in the following
example using the FETCH FIRST clause.
SELECT * FROM T FETCH FIRST 10 ROWS ONLY
This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP
SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c
and Mimer SQL.
Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is
considered part of the ORDER BY clause. The ORDER BY, OFFSET, and
FETCH FIRST clauses are all required for this usage.
SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10
ROWS ONLY
You can see, that the ONLY
keyword seems to be part of the standard definition. Below is a table where some implemented a syntax not following the standard:
So, in case of SQL Server
the only
keyword is a must when you are using ORDER BY
and FETCH
. This seems to be following the standard, so do not waste too much time to worry about it.
answered Nov 13 '18 at 6:41
gotqn
19.7k32111189
19.7k32111189
add a comment |
add a comment |
The OFFSET FETCH
is used for pagination purpose ie; to retrieve only some rows as specified
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
So here it won't return the first 10 rows when ordering by n because it is specified as the offset . Then it will return the remaining 5 rows only
ONLY
is used to specify the rows needed.
If it is not specified then the query will skip the first 10 rows from the sorted result set and return the remaining rows.
If you only want to offset the rows the use OFFSET
keyword only.
If you need to limit the number of rows returned then use FETCH
with ONLY
DEMO
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
@FrenkyB i have updated the answer and included a demo for you.ONLY
is a part of fetch but not part offset.
– Sanal Sunny
Nov 13 '18 at 6:34
add a comment |
The OFFSET FETCH
is used for pagination purpose ie; to retrieve only some rows as specified
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
So here it won't return the first 10 rows when ordering by n because it is specified as the offset . Then it will return the remaining 5 rows only
ONLY
is used to specify the rows needed.
If it is not specified then the query will skip the first 10 rows from the sorted result set and return the remaining rows.
If you only want to offset the rows the use OFFSET
keyword only.
If you need to limit the number of rows returned then use FETCH
with ONLY
DEMO
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
@FrenkyB i have updated the answer and included a demo for you.ONLY
is a part of fetch but not part offset.
– Sanal Sunny
Nov 13 '18 at 6:34
add a comment |
The OFFSET FETCH
is used for pagination purpose ie; to retrieve only some rows as specified
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
So here it won't return the first 10 rows when ordering by n because it is specified as the offset . Then it will return the remaining 5 rows only
ONLY
is used to specify the rows needed.
If it is not specified then the query will skip the first 10 rows from the sorted result set and return the remaining rows.
If you only want to offset the rows the use OFFSET
keyword only.
If you need to limit the number of rows returned then use FETCH
with ONLY
DEMO
The OFFSET FETCH
is used for pagination purpose ie; to retrieve only some rows as specified
SELECT N FROM TEST_CTE
ORDER BY N
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
So here it won't return the first 10 rows when ordering by n because it is specified as the offset . Then it will return the remaining 5 rows only
ONLY
is used to specify the rows needed.
If it is not specified then the query will skip the first 10 rows from the sorted result set and return the remaining rows.
If you only want to offset the rows the use OFFSET
keyword only.
If you need to limit the number of rows returned then use FETCH
with ONLY
DEMO
edited Nov 13 '18 at 6:30
answered Nov 13 '18 at 6:21
Sanal Sunny
6628
6628
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
@FrenkyB i have updated the answer and included a demo for you.ONLY
is a part of fetch but not part offset.
– Sanal Sunny
Nov 13 '18 at 6:34
add a comment |
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
@FrenkyB i have updated the answer and included a demo for you.ONLY
is a part of fetch but not part offset.
– Sanal Sunny
Nov 13 '18 at 6:34
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
Thanks - but is keyword ONLY described in detail anywhere? Just this keyword? Or is it simply part of offset - fetch? If you miss this keyword, there's syntax error. So it must be specified.
– FrenkyB
Nov 13 '18 at 6:23
@FrenkyB i have updated the answer and included a demo for you.
ONLY
is a part of fetch but not part offset.– Sanal Sunny
Nov 13 '18 at 6:34
@FrenkyB i have updated the answer and included a demo for you.
ONLY
is a part of fetch but not part offset.– Sanal Sunny
Nov 13 '18 at 6:34
add a comment |
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%2f53274875%2fonly-keyword-meaning%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
1
technet.microsoft.com/en-us/library/…
– Mitch Wheat
Nov 13 '18 at 6:15
Thanks, I've seen this - but keyword only is just used there. No word about it. Is anywhere explanation about this keyword solely?
– FrenkyB
Nov 13 '18 at 6:20
1
"Why is it necessary to have it there?" because that's what the authors of T-SQL decided. Why did they decide this? I don't know.
– Zohar Peled
Nov 13 '18 at 6:36
1
We can say like it's part of sql syntax for FETCH NEXT.
– NP007
Nov 13 '18 at 6:39