ONLY keyword - meaning












2














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?










share|improve this question


















  • 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
















2














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?










share|improve this question


















  • 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














2












2








2







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?










share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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














  • 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












2 Answers
2






active

oldest

votes


















2














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:



enter image description here



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.






share|improve this answer





























    1














    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






    share|improve this answer























    • 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













    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


















    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









    2














    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:



    enter image description here



    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.






    share|improve this answer


























      2














      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:



      enter image description here



      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.






      share|improve this answer
























        2












        2








        2






        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:



        enter image description here



        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.






        share|improve this answer












        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:



        enter image description here



        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 13 '18 at 6:41









        gotqn

        19.7k32111189




        19.7k32111189

























            1














            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






            share|improve this answer























            • 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


















            1














            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






            share|improve this answer























            • 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
















            1












            1








            1






            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






            share|improve this answer














            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







            share|improve this answer














            share|improve this answer



            share|improve this answer








            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




















            • 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




















            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%2f53274875%2fonly-keyword-meaning%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

            Bressuire

            Vorschmack

            Quarantine