{"id":111,"date":"2013-07-04T09:32:18","date_gmt":"2013-07-04T09:32:18","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=111"},"modified":"2016-01-12T12:39:55","modified_gmt":"2016-01-12T12:39:55","slug":"how-to-get-highest-paid-employee-by-department","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/how-to-get-highest-paid-employee-by-department\/","title":{"rendered":"How to get Highest Paid Employee by Department"},"content":{"rendered":"<p>I have a Table with rows like one below<\/p>\n<p><img decoding=\"async\" alt=\"Image\" src=\"http:\/\/codethataint.com\/blog\/wp-content\/uploads\/2013\/07\/stack.png\" \/><\/p>\n<p>I want to have Highest paid employee in Each Department<\/p>\n<p><strong>EmpName\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong> <strong>Designation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong><strong>Salary<\/strong><br \/>\nPhillipe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Analyst \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 3150<br \/>\nScott\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Clerk \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 2500<br \/>\nMac \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 Manager \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 5000<\/p>\n<p><strong>Query 1(Oracle Specific)<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT EmpName, Designation, Salary\r\n  FROM (SELECT EmpName, Designation, Salary,\r\n               RANK() OVER (PARTITION BY Designation ORDER BY Salary DESC) AS rn\r\n          FROM Employees)\r\n WHERE rn = 1\r\n ORDER BY Designation;\r\n<\/pre>\n<p><strong>Query 2<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n SELECT EmpName, Designation, Salary\r\n   FROM Employees\r\n  WHERE (Designation, Salary) IN (SELECT Designation, MAX(Salary)\r\n                                   FROM Employees\r\n                                  GROUP BY Designation)\r\n ORDER BY Designation;\r\n<\/pre>\n<p><strong>Query 3<strong>(Oracle Specific)<\/strong><\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT max(EmpName) keep (dense_rank last order by salary) AS EmpName,\r\n       Designation, max(Salary) keep (dense_rank last order by salary)  AS Salary\r\n  FROM Employees\r\n GROUP BY Designation;\r\n<\/pre>\n<p><strong>Query 4<strong>(Oracle Specific)<\/strong><\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT max(EmpName) keep (dense_rank last order by salary) AS EmpName,\r\n       Designation, max(Salary) keep (dense_rank last order by salary)  AS Salary\r\n  FROM Employees\r\n GROUP BY Designation;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I have a Table with rows like one below I want to have Highest paid employee in Each Department EmpName\u00a0\u00a0\u00a0\u00a0\u00a0 Designation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Salary Phillipe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Analyst \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 3150 Scott\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Clerk \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 2500 Mac \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0&hellip; <a href=\"https:\/\/codethataint.com\/blog\/how-to-get-highest-paid-employee-by-department\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[],"class_list":["post-111","post","type-post","status-publish","format-standard","hentry","category-queries-oracle-database-2"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=111"}],"version-history":[{"count":7,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/111\/revisions"}],"predecessor-version":[{"id":1111,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/111\/revisions\/1111"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}