{"id":580,"date":"2017-09-18T23:05:56","date_gmt":"2017-09-18T23:05:56","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=580"},"modified":"2020-05-19T04:11:04","modified_gmt":"2020-05-19T04:11:04","slug":"uso-de-with-ties-en-consultas-select","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=580","title":{"rendered":"Uso de With Ties en Select SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"244\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_D.png\" alt=\"\" class=\"wp-image-1088\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>With Ties en Select<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li>La opci\u00f3n With Ties en una instrucci\u00f3n&nbsp;select se utiliza cuando se usa la cl\u00e1usula Top n [Percent]. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=289\">Ver Opciones de Select<\/a>)<\/li><li>El uso de With ties en el listado permite mostrar los registros&nbsp;que tienen los mismos valores que el \u00faltimo mostrado de acuerdo a la cantidad mostrada por Top n o Top n Percent.<\/li><li>El uso de Top debe ir acompa\u00f1ado con la cl\u00e1usula Order By. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=211\">Ver Ordenamientos<\/a>)<\/li><\/ul>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Ejemplo<\/strong><\/h2>\n\n\n\n<p>Para entender el uso de With ties vamos a crear una base de datos, luego crear una tabla de Alumnos e insertar registros los cuales van a tener un campo de Promedios.<\/p>\n\n\n\n<p>Create database PruebaTies<br>go<\/p>\n\n\n\n<p>use PruebaTies<br>go<\/p>\n\n\n\n<p>Create table Alumnos<br>(AlumnosCodigo nchar(6),<br>AlumnosNombre nvarchar(50),<br>AlumnosPromedio Numeric(9,2),<br>constraint AlumnosPK Primary key (AlumnosCodigo))<br>go<\/p>\n\n\n\n<p>&#8212; Los registros<\/p>\n\n\n\n<p>Insert into Alumnos values<br>(&#8216;050426&#8242;,&#8217;Carlos Balarezo&#8217;,12),<br>(&#8216;798455&#8242;,&#8217;Aracely Terravova&#8217;,17),<br>(&#8216;230974&#8242;,&#8217;M\u00f3nica Mendoza&#8217;,16),<br>(&#8216;094569&#8242;,&#8217;Sergio Campos&#8217;,14),<br>(&#8216;092395&#8242;,&#8217;Rossana Llanos&#8217;,9),<br>(&#8216;077845&#8242;,&#8217;Pedro Alc\u00e1ntara&#8217;,14),<br>(&#8216;509875&#8242;,&#8217;Patricia L\u00f3pez&#8217;,7)<br>go<\/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><strong>&#8212; Listado de los Alumnos ordenados por promedio en orden descendente<\/strong><\/p>\n\n\n\n<p>select * from Alumnos order by AlumnosPromedio desc<br>go<\/p>\n\n\n\n<p>Note que en el conjunto de resultados hay dos registros que tienen igual promedio, que son el tercero y el cuarto.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_01.png\" alt=\"\" class=\"wp-image-1085\" width=\"798\" height=\"318\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_01.png 882w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_01-300x120.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_01-768x307.png 768w\" sizes=\"auto, (max-width: 798px) 100vw, 798px\" \/><figcaption>Alumnos ordenados por promedio. Note que existen dos alumnos con promedio 14.<\/figcaption><\/figure><\/div>\n\n\n\n<p><strong>&#8212; Mostrar los 3 primeros, sin el uso de with Ties.<\/strong><\/p>\n\n\n\n<p>select Top 3 * from Alumnos order by AlumnosPromedio desc<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_02.png\" alt=\"\" class=\"wp-image-1086\" width=\"782\" height=\"203\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_02.png 986w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_02-300x78.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_02-768x199.png 768w\" sizes=\"auto, (max-width: 782px) 100vw, 782px\" \/><\/figure><\/div>\n\n\n\n<p>Puede visualizarse solamente los tres primeros, en la figura donde se &nbsp;muestran todos los alumnos puede notarse que el cuarto alumno tiene el mismo promedio que el tercero.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Usando With Ties<\/strong><\/h3>\n\n\n\n<p>&#8212; Mostrar los 3 primeros, usando with Ties.<\/p>\n\n\n\n<p>select Top 3 with ties * from Alumnos<br>order by AlumnosPromedio desc<br>go<\/p>\n\n\n\n<p>Note que en la instrucci\u00f3n se est\u00e1 especificando que se muestren 3 registros al usar la cl\u00e1usula Top 3, pero al incluir la opci\u00f3n<strong> With Ties<\/strong> tambi\u00e9n se muestra el cuarto registros porque tiene el mismo promedio que el tercero.<\/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\/ManualSQL_withTies_03.png\" alt=\"\" class=\"wp-image-1087\" width=\"829\" height=\"349\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_03.png 741w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_withTies_03-300x126.png 300w\" sizes=\"auto, (max-width: 829px) 100vw, 829px\" \/><\/figure>\n\n\n\n<p>Si no hubieran registros con el mismo valor del promedio iguales al \u00faltimo, solamente se muestran los tres primeros.<\/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","protected":false},"excerpt":{"rendered":"<p>With Ties en Select La opci\u00f3n With Ties en una instrucci\u00f3n&nbsp;select se utiliza cuando se usa la cl\u00e1usula Top n [Percent]. (Ver Opciones de Select) El uso de With ties en el listado permite mostrar los registros&nbsp;que tienen los mismos valores que el \u00faltimo mostrado de acuerdo a la cantidad mostrada por Top n o &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=580\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1088,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,9],"tags":[28,46,47,51,57],"class_list":["post-580","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-registros-vistas","tag-esquemas","tag-particion","tag-permisos","tag-select","tag-tablas","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/580","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=580"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/580\/revisions"}],"predecessor-version":[{"id":1090,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/580\/revisions\/1090"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1088"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}