{"id":18866,"date":"2022-11-02T05:22:00","date_gmt":"2022-11-01T23:52:00","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/"},"modified":"2022-11-02T05:22:00","modified_gmt":"2022-11-01T23:52:00","slug":"solved-sql-join-with-unique-rows","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/","title":{"rendered":"[Solved] SQL Join with unique rows"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-40041348\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"40041348\" data-parentid=\"40038840\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Ok, let&#8217;s see if this answer suits your request.<\/p>\n<pre><code>SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN \n   FROM \n   (\n   select  EFFDT,GTN,EMPLID,DEDCD,\n        row_number() over (partition by EMPLID order by DEDCD) rn\n from table1 ) A\n    LEFT OUTER JOIN \n    (\n   select  EFFDT,EMPLID,DEDCD,\n        row_number() over (partition by EMPLID order by DEDCD) rn\n from table2       \n    ) B\n ON ( A.EMPLID=B.EMPLID AND A.EFFDT=B.EFFDT AND a.rn=b.rn)\n<\/code><\/pre>\n<p>I&#8217;m assuming here you&#8217;re just interested in having side by side the info of the two tables linked by EMPLID and EFFDT, and that table1 and table2 will always have the same record number for the same key.<\/p>\n<p>I&#8217;ve created an example for you to test at <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/rextester.com\/PPUQ61243\">rextester.com<\/a>.<\/p>\n<p><strong>Edited: new version<\/strong><\/p>\n<p>I&#8217;ve tried to manage also the new &#8220;requirements&#8221; that have been submitted here as comments.<br \/>\nIt&#8217;s getting complicated, but I hope that&#8217;s what you need:<\/p>\n<pre><code> SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN\n    FROM \n    (\n    select  t1.EFFDT,t1.GTN,t1.EMPLID,t1.DEDCD,\n         row_number() over (partition by t1.EMPLID order by case when t2.EMPLID is not null then 0 else 1 end asc, t1.DEDCD) rn\n  from table1 t1 left join (select distinct EMPLID,   EFFDT, DEDCD from    table2) t2 on  t1.EMPLID=t2.EMPLID AND t1.EFFDT=t2.EFFDT  and t1.DEDCD=t2.DEDCD\n        ) A\n     LEFT OUTER JOIN \n     (\n    select  t2.EFFDT,t2.EMPLID,t2.DEDCD,\n         row_number() over (partition by t2.EMPLID order by case when t1.EMPLID is not null then 0 else 1 end asc, t2.DEDCD) rn\n  from table2 t2  left join (select distinct EMPLID,   EFFDT, DEDCD from    table1) t1 on  t1.EMPLID=t2.EMPLID AND t1.EFFDT=t2.EFFDT and t1.DEDCD=t2.DEDCD\n     ) B\n  ON ( A.EMPLID=B.EMPLID AND A.EFFDT=B.EFFDT AND a.rn=b.rn)\n<\/code><\/pre>\n<p>I&#8217;ve created an example for you to test at <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/rextester.com\/QYXZ77188\">rextester.com<\/a><\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">12<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved SQL Join with unique rows <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Ok, let&#8217;s see if this answer suits your request. SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,&#8217;YYYY-MM-DD&#8217;) EFFDT, b.DEDCD as DEDCD2,GTN FROM ( select EFFDT,GTN,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table1 ) A LEFT OUTER JOIN ( select EFFDT,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table2 ) B ON ( &#8230; <a title=\"[Solved] SQL Join with unique rows\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\" aria-label=\"More on [Solved] SQL Join with unique rows\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[534,838,341],"class_list":["post-18866","post","type-post","status-publish","format-standard","hentry","category-solved","tag-join","tag-oracle","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] SQL Join with unique rows - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL Join with unique rows - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Ok, let&#8217;s see if this answer suits your request. SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,&#039;YYYY-MM-DD&#039;) EFFDT, b.DEDCD as DEDCD2,GTN FROM ( select EFFDT,GTN,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table1 ) A LEFT OUTER JOIN ( select EFFDT,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table2 ) B ON ( ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-01T23:52:00+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL Join with unique rows\",\"datePublished\":\"2022-11-01T23:52:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\"},\"wordCount\":119,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"join\",\"oracle\",\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\",\"name\":\"[Solved] SQL Join with unique rows - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-11-01T23:52:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL Join with unique rows\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL Join with unique rows - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL Join with unique rows - JassWeb","og_description":"[ad_1] Ok, let&#8217;s see if this answer suits your request. SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN FROM ( select EFFDT,GTN,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table1 ) A LEFT OUTER JOIN ( select EFFDT,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table2 ) B ON ( ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/","og_site_name":"JassWeb","article_published_time":"2022-11-01T23:52:00+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL Join with unique rows","datePublished":"2022-11-01T23:52:00+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/"},"wordCount":119,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["join","oracle","sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/","name":"[Solved] SQL Join with unique rows - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-11-01T23:52:00+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-join-with-unique-rows\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL Join with unique rows"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18866","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=18866"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18866\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=18866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=18866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=18866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}