title: SQL Query using Partition By
body: <p>I have following table name JobTitle</p>
  
  <pre><code>JobID LanaguageID
  -----------------
  1         1
  1         2
  1         3
  2         1
  2         2 
  3         4
  4         5
  5         2
  </code></pre>
  
  <p>I am selecting all records from table except duplicate JobID's for which count > 1. I am selecting only one record/first row from the duplicate JobID's.
  Now I am passing LanguageID as paramter to stored procedure and I want to select duplicate JobID for that languageID along with the other records Also.
  If I have passed languageID as 1 then output should come as follows</p>
  
  <pre><code>JobID LanaguageID
  -----------------
  1         1
  2         1
  3         4
  4         5
  5         2
  </code></pre>
  
  <p>I have tried using following query.</p>
  
  <pre><code>with CTE_RN as
  (
      SELECT ROW_NUMBER() OVER(PARTITION BY JobTitle.JobID ORDER BY JobTitle.JobTitle) AS RN
      FROM JobTitle
      INNER JOIN JobTitle_Lang
          ON JobTitle.JobTitleID = JobTitle_Lang.JobTitleID
  )
  </code></pre>
  
  <p>But I am unable to use WHERE clause in the above query.
  Is any different approch should be followed. Or else how can i modify the query to get the desired output</p>