{"id":1788,"date":"2020-05-25T04:40:33","date_gmt":"2020-05-25T04:40:33","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1788"},"modified":"2020-05-25T04:40:34","modified_gmt":"2020-05-25T04:40:34","slug":"uso-de-offset-y-fetch-next-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1788","title":{"rendered":"Uso de Offset y Fetch Next en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"271\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select___D.png\" alt=\"\" class=\"wp-image-1789\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Usando OffSet y Fetch next en Select<\/h2>\n\n\n\n<p>La instrucci\u00f3n Select (Ver Select) tiene varias opciones que se pueden incluir seg\u00fan las necesidades de la presentaci\u00f3n de la informaci\u00f3n extra\u00edda de una o m\u00e1s tablas.<br>En ocasiones es necesario mostrar los registros en un determinado orden, para ello podemos usar la cl\u00e1usula Order by <a href=\"https:\/\/manualsqlserver.com\/?p=211\" target=\"_blank\" rel=\"noreferrer noopener\">(Ver Ordenamientos<\/a>), lo que permitir\u00e1 mostrar el resultado obtenido con un orden especificado, y de acuerdo a uno o m\u00e1s campos.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p style=\"font-size:24px\"><strong>Como usar Offset en SQL Server<\/strong><\/p>\n\n\n\n<p><strong>Offset n Rows<\/strong> permite en un listado que se muestra de manera ordenada no tener en cuenta los n registros que se muestran al inicio del listado,  Offset n Rows se utiliza despu\u00e9s de la cl\u00e1usula Order by donde el valor n especifica la cantidad de registros a no tener en cuenta.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejemplos<\/strong><\/p>\n\n\n\n<p>Usando la base de datos Northwind<br>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Listar los productos ordenados por stock en orden descendente<\/strong><br>Select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;<br>from Products As P<br>order by P.UnitsInStock desc<br>go<br>Se puede notar que aparecen todos los productos ordenados por unidades en Stock. En la imagen se han marcado los 5 primeros registros que no se tendr\u00e1n en cuenta en la siguiente instrucci\u00f3n usando OffSet<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__01.png\" alt=\"\" class=\"wp-image-1790\" width=\"752\" height=\"506\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__01.png 950w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__01-300x202.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__01-768x517.png 768w\" sizes=\"auto, (max-width: 752px) 100vw, 752px\" \/><\/figure>\n\n\n\n<p><strong>Para no presentar en el listado los cinco primeros productos con mayor stock podemos usar la cl\u00e1usula Offset especificando 5 registros (OffSet 5 rows). Note que el sexto registro es el que tiene el c\u00f3digo 33.<\/strong><br>Select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;<br>from Products As P<br>order by P.UnitsInStock desc<br>Offset 5 Rows<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__02.png\" alt=\"\" class=\"wp-image-1791\" width=\"616\" height=\"546\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__02.png 704w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__02-300x266.png 300w\" sizes=\"auto, (max-width: 616px) 100vw, 616px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Listar los productos que se vendieron mas en 1997, no tenga en cuenta los 10 primeros. (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\">Ver Joins<\/a>) (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=225\" target=\"_blank\">Ver Agrupamientos<\/a>) (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=207\" target=\"_blank\">Ver Funciones de agregado<\/a>). Primero mostrar todos los productos<br><\/strong>Select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;,<br>sum(D.Quantity) As &#8216;Cantidad Vendida&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on O.OrderID = D.OrderID<br>Where Year(O.OrderDate) = 1997<br>Group by P.ProductID , P.ProductName, P.UnitPrice, P.UnitsInStock<br>order by [Cantidad Vendida] desc<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03-1024x689.png\" alt=\"\" class=\"wp-image-1792\" width=\"701\" height=\"471\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03-1024x689.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03-300x202.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03-768x517.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03.png 1031w\" sizes=\"auto, (max-width: 701px) 100vw, 701px\" \/><\/figure>\n\n\n\n<p><strong>Ahora se incluye la opci\u00f3n Offset 10 rows para que los 10 primeros no sean tomados en cuenta.<\/strong><br>Select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;,<br>sum(D.Quantity) As &#8216;Cantidad Vendida&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on O.OrderID = D.OrderID<br>Where Year(O.OrderDate) = 1997<br>Group by P.ProductID , P.ProductName, P.UnitPrice, P.UnitsInStock<br>order by [Cantidad Vendida] desc<br>Offset 10 rows<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03a.png\" alt=\"\" class=\"wp-image-1793\" width=\"685\" height=\"624\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03a.png 764w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__03a-300x273.png 300w\" sizes=\"auto, (max-width: 685px) 100vw, 685px\" \/><\/figure>\n\n\n\n<p><strong>Los productos que no se presentan son los 10 que tienen mas venta, para mostrarlos podemos usar la cl\u00e1usula Top (Ver Select &#8211; Opciones)<br><\/strong>Select Top 10<br>P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;,<br>sum(D.Quantity) As &#8216;Cantidad Vendida&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>Group by P.ProductID , P.ProductName, P.UnitPrice, P.UnitsInStock<br>order by [Cantidad Vendida] desc<br>go<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Como usar Fetch next n rows only<\/strong><\/p>\n\n\n\n<p>Para listar los registros teniendo en cuenta un orden espec\u00edfico e incluir una cantidad determinada de estos se puede usar la cl\u00e1usula Fetch next n Rows only, lo que permitir\u00e1 especificar cuantos registros se desea mostrar, exceptuando los primeros que no se presentan usando Offset n Rows<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Listar los empleados y la cantidad de \u00f3rdenes generadas, no mostrar los 4 primeros e incluir solamente tres registros en el listado. Ver Funci\u00f3n Concat_ws <\/strong><\/p>\n\n\n\n<p><strong>Primero vamos a ver el listado completo de los empleados y la cantidad de \u00f3rdenes.<\/strong><br>Select E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;,<br>CONCAT_WS(&#8216;, &#8216;,E.LastName,E.FirstName) As &#8216;Empleado&#8217;,<br>COUNT(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID<br>Group by E.EmployeeID, CONCAT_WS(&#8216;, &#8216;,E.LastName,E.FirstName)<br>order by [Cantidad de \u00d3rdenes] desc<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__05.png\" alt=\"\" class=\"wp-image-1795\" width=\"724\" height=\"298\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__05.png 921w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__05-300x124.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__05-768x317.png 768w\" sizes=\"auto, (max-width: 724px) 100vw, 724px\" \/><\/figure>\n\n\n\n<p><strong>Ahora el listado no tendr\u00e1 en cuenta los 4 primeros y se va a listar \u00fanicamente los siguientes 3 registros.<\/strong><br>Select E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;,<br>CONCAT_WS(&#8216;, &#8216;,E.LastName,E.FirstName) As &#8216;Empleado&#8217;,<br>COUNT(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID<br>Group by E.EmployeeID, CONCAT_WS(&#8216;, &#8216;,E.LastName,E.FirstName)<br>order by [Cantidad de \u00d3rdenes] desc<br>Offset 4 rows fetch next 3 rows only<br>go<br>Compare las dos im\u00e1genes anteriores y puede notar que no se muestran los 4 primeros y luego se muestran los siguientes tres registros.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__06.png\" alt=\"\" class=\"wp-image-1796\" width=\"711\" height=\"194\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__06.png 672w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_UsoFetchNext_Select__06-300x82.png 300w\" sizes=\"auto, (max-width: 711px) 100vw, 711px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Usando OffSet y Fetch next en Select La instrucci\u00f3n Select (Ver Select) tiene varias opciones que se pueden incluir seg\u00fan las necesidades de la presentaci\u00f3n de la informaci\u00f3n extra\u00edda de una o m\u00e1s tablas.En ocasiones es necesario mostrar los registros en un determinado orden, para ello podemos usar la cl\u00e1usula Order by (Ver Ordenamientos), lo &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1788\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1789,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[86,85],"class_list":["post-1788","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","tag-fetch-next","tag-offset","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1788","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1788"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1788\/revisions"}],"predecessor-version":[{"id":1797,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1788\/revisions\/1797"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1789"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1788"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}