{"id":211,"date":"2017-09-18T23:18:21","date_gmt":"2017-09-18T23:18:21","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=211"},"modified":"2021-03-20T20:01:00","modified_gmt":"2021-03-20T20:01:00","slug":"211","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=211","title":{"rendered":"Ordenamientos en SQL Server &#8211; Order by"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Ordenamientos.png\" alt=\"\" class=\"wp-image-1050\" width=\"319\" height=\"253\"\/><\/figure>\n\n\n\n<p>En muchas ocasiones se requiere listar los registros en un determinado orden, para esto se debe utilizar de manera correcta la cl\u00e1usula Order by de Select, se recomienda el uso de \u00edndices en los campos por los cuales se va a ordenar. (<a href=\"https:\/\/manualsqlserver.com\/?p=158\">Ver Indices<\/a>)<\/p>\n\n\n\n<!--more-->\n\n\n\n<h3 class=\"wp-block-heading\">Ordenamientos<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>La Cl\u00e1usula Order By, debe ir al final de la instrucci\u00f3n Select, salvo que se use Offset y Fetch Next  (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=1788\" target=\"_blank\">Ver OffSet Fetch Next<\/a>)<\/li><li>Se puede usar el nombre de campo, el al\u00edas o el n\u00famero de orden del campo para especificar el orden.<\/li><li>Se puede usar Desc y Asc (por defecto), se puede ordenar por varios campos<\/li><li>No se puede ordenar por los campos ntext, text, image, geography, geometry, y xml.<\/li><li>Es posible usar un campo de la tabla que no aparece en la lista de campos como el campo de ordenamiento.<\/li><li>Se sugiere mostrar los registros en las aplicaciones en controles que permitan el ordenamiento y no usar la cl\u00e1usula order by para extraerlos ordenados de la base de datos.<\/li><\/ul>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p>Usando Northwind<br><strong>use Northwind<br>go<\/strong><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Listar los empleados ordenados por Apellidos<\/strong><br>select * from Employees order by LastName<br>go<\/p>\n\n\n\n<p>El resultado se muestra en la siguiente imagen.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_01.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_01.png\" alt=\"\" class=\"wp-image-2527\" width=\"881\" height=\"332\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_01.png 1001w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_01-300x113.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_01-768x290.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_01-795x300.png 795w\" sizes=\"auto, (max-width: 881px) 100vw, 881px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio <\/strong>2<\/p>\n\n\n\n<p><strong>Listar los clientes (Customers) ordenados por nombre<\/strong><br>select C.CustomerID As &#8216;C\u00f3d. Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217; from Customers As C order by C.CompanyName<br>go<\/p>\n\n\n\n<p>El resultado se muestra en la siguiente imagen.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_02.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_02.png\" alt=\"\" class=\"wp-image-2528\" width=\"653\" height=\"737\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_02.png 523w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_02-266x300.png 266w\" sizes=\"auto, (max-width: 653px) 100vw, 653px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Listar los productos ordenados por Id. de Categoria y luego por Precio unitario.<\/strong><br>select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;, P.CategoryID As &#8216;C\u00f3d. Categoria&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217; from Products As P<br>order by CategoryID desc, precio asc<br>go<\/p>\n\n\n\n<p>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_03.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_03.png\" alt=\"\" class=\"wp-image-2529\" width=\"817\" height=\"671\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_03.png 718w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_03-300x247.png 300w\" sizes=\"auto, (max-width: 817px) 100vw, 817px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p><strong>Listar los empleados ordenados por fecha de ingreso en orden descendente<\/strong><br>select E.EmployeeID, E.LastName, E.FirstName,<br><strong>Format<\/strong>(E.HireDate,&#8217;dd\/MM\/yyy&#8217;) As &#8216;Fecha de Ingreso&#8217;<br>from Employees As E order by E.HireDate desc<br>go<br><strong>&#8212; Note el uso de Format en la fecha de ingreso (HireDate)<\/strong><\/p>\n\n\n\n<p>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_04.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_04.png\" alt=\"\" class=\"wp-image-2530\" width=\"724\" height=\"433\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_04.png 583w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_04-300x180.png 300w\" sizes=\"auto, (max-width: 724px) 100vw, 724px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 5<\/strong><\/p>\n\n\n\n<p><strong>Ordenar los productos de las categorias 3,5 y 8 que tienen stock primero por categoria en orden ascendente y el stock en orden descendente.<\/strong><br>Select P.ProductID As &#8216;C\u00f3digo&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>Format(P.UnitPrice, &#8216;###,##0.00&#8217;) As &#8216;Precio&#8217;,<br>P.UnitsInStock As &#8216;Stock&#8217;, P.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217; from Products As P<br>where P.CategoryID in (3,5,8) and P.UnitsInStock >0<br>order by P.CategoryID asc, P.UnitsInStock desc<br>go<\/p>\n\n\n\n<p>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_05.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_05.png\" alt=\"\" class=\"wp-image-2531\" width=\"785\" height=\"635\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_05.png 695w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/03\/ManualSQL_Ordenamientos_05-300x243.png 300w\" sizes=\"auto, (max-width: 785px) 100vw, 785px\" \/><\/a><\/figure>\n\n\n\n<p class=\"has-very-light-gray-background-color has-background\"><strong>Importante<\/strong><br>Para el ordenamiento se puede usar el nombre de campo, el alias o el n\u00famero de orden de aparici\u00f3n del campo despu\u00e9s de Order By<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 6<\/strong><\/p>\n\n\n\n<p><strong>Listado de los proveedores (Suppliers) ordenados por pa\u00eds y por Nombre de compa\u00f1\u00eda<\/strong><br><strong>Usando el nombre de campo<\/strong><br>select S.SupplierID As &#8216;C\u00f3digo&#8217;, S.CompanyName As &#8216;Empresa&#8217;, S.Country As &#8216;Pa\u00eds&#8217;<br>from Suppliers as S order by <strong>S.Country, S.CompanyName<\/strong><br>go<br><strong>Usando el alias del campo<\/strong><br>select S.SupplierID As &#8216;C\u00f3digo&#8217;, S.CompanyName As &#8216;Empresa&#8217;, S.Country As &#8216;Pa\u00eds&#8217;<br>from Suppliers as S order by <strong>Pa\u00eds, Empresa<\/strong><br>go<br><strong>Usando el orden de aparici\u00f3n del campo<\/strong><br>select S.SupplierID As &#8216;C\u00f3digo&#8217;, S.CompanyName As &#8216;Empresa&#8217;, S.Country As &#8216;Pa\u00eds&#8217;<br>from Suppliers as S order by <strong>3, 2<\/strong><br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>En muchas ocasiones se requiere listar los registros en un determinado orden, para esto se debe utilizar de manera correcta la cl\u00e1usula Order by de Select, se recomienda el uso de \u00edndices en los campos por los cuales se va a ordenar. (Ver Indices)<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=211\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1050,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[53],"class_list":["post-211","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","tag-sqlserver","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/211","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=211"}],"version-history":[{"count":3,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/211\/revisions"}],"predecessor-version":[{"id":2532,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/211\/revisions\/2532"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1050"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}