title: Find the 3rd Maximum Salary for each department based on table data
body: <p>I need to find out the <code>3rd maximum salary</code> for an employee for each department in a <code>table</code>. if no <code>3rd maximum salary</code> exists then display <code>2nd maximum salary</code>. if no <code>2nd maximum salary</code> exist then find the <code>highest salary</code>. How to achieve this result in <code>sql-server</code>?</p>
  
  <p>The <code>table</code> structure is given below</p>
  
  <pre><code>create table employee1(empid int, empname varchar(10), deptid int, salary money)
  
  insert into employee1
  select 1,'a',1, 1000
  union
  select 1,'b',1, 1200 
  union
  select 1,'c',1, 1500 
  union
  select 1,'c',1, 15700 
  union
  select 1,'d',2, 1000 
  union
  select 1,'e',2, 1200 
  union
  select 1,'g',3, 1500 
  </code></pre>
  
  <p>I have tried the common way of getting the maximum salary for each category using <code>row_number</code> function.</p>
  
  <pre><code>;with cte
  as
  ( 
  select ROW_NUMBER( ) over( partition by deptid order by salary) as id, * from employee1 
  )
  select * from cte
  </code></pre>