title: Remove duplicates based on one column
body: <p>I have to join multiple tables in a SQL Server database in order to use the fields in Report Builder. This SQL query is ran as a stored procedure. The problem is that it is returning multiple rows that I consider duplicates.</p>
  
  <p>Here is my query (some structure parts were taken out): </p>
  
  <pre><code> ALTER PROCEDURE [dbo].[GetRollCallData] 
      @Ids        VARCHAR(255),
      @LexiconId  INT,
      @UUID       UNIQUEIDENTIFIER,
      @ReadOnly   INT
   AS
  
  
   DECLARE @TableCode INT
   SET @TableCode = 58
  
  
          EXEC InsertInSelectionCache @Ids, @UUID, @TableCode, 0
  
              SELECT [File].*, FileType2Lexicon.Label as FileTypeLabel, [People].DefaultPhone, CFV.Value as DOA, CFV.CustomRecordsetId
                  FROM FileType2Lexicon, SelectionCache, [People], [File]
                  INNER JOIN [CustomRecordSet]
                  ON [CustomRecordset].RecordId = [File].Id
                  INNER JOIN (SELECT Value, CustomFieldId, CustomRecordsetId FROM [CustomFieldValue]) CFV
                  ON  [CustomRecordset].Id = CFV.CustomRecordsetId
                  INNER JOIN (SELECT CustomFieldId, Label FROM [CustomField2Lexicon]) CF2L
                  ON CF2L.CustomFieldId = CFV.CustomFieldId
                  WHERE   [File].Id = SelectionCache.RecordId
                  AND SelectionCache.UUID = @UUID
                  AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
                  AND     [File].Id &lt;&gt; 0 
                  AND     [File].FileTypeId = FileType2Lexicon.FileTypeId 
                  AND     FileType2Lexicon.LexiconId = @LexiconId
                  AND     [File].ClientIdString = [People].ClientIdString
                  AND     CFV.Value &lt;&gt; ''
                  AND     CF2L.Label = 'Date of Arrest'
  </code></pre>
  
  <p>This ends up returning:</p>
  
  <pre><code>Id  ShortFileNameMatterName Summary             FileTypeLabelDefaultPhone   DOA         CustomRecordsetId
  4691    Bob State v.  Bob   RETAINED:1/1/2013   Criminal    (123) 242-8454  2013-01-16  6942
  4691    Bob State v.  Bob   RETAINED:1/1/2013   Criminal    (123) 242-8454  2013-01-16  6948
  </code></pre>
  
  <p>As apparent, the rows are duplicating because of the recordsetId column. Each table row does have it's own UUID as well.</p>
  
  <p>Any ideas? I've been working on this for days.</p>
  
  <p>EDIT:
  Here is my solution that I found.</p>
  
  <pre><code>    WITH RankedCTE AS(
          SELECT ROW_NUMBER() OVER(PARTITION BY [File].Id ORDER BY CustomRecordsetId DESC) AS RowNumber, [File].*, FileType2Lexicon.Label as FileTypeLabel, [People].DefaultPhone, [People].InvertedName, CFV.Value as DOA
              FROM FileType2Lexicon, SelectionCache, [People], [File]
              INNER JOIN [CustomRecordSet]
              ON [CustomRecordset].RecordId = [File].Id
              INNER JOIN (SELECT Value, CustomFieldId, CustomRecordsetId FROM [CustomFieldValue]) CFV
              ON  [CustomRecordset].Id = CFV.CustomRecordsetId
              INNER JOIN (SELECT CustomFieldId, Label FROM [CustomField2Lexicon]) CF2L
              ON CF2L.CustomFieldId = CFV.CustomFieldId
              WHERE   [File].Id = SelectionCache.RecordId
              AND SelectionCache.UUID = @UUID
              AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
              AND     [File].Id &lt;&gt; 0 
              AND     [File].FileTypeId = FileType2Lexicon.FileTypeId 
              AND     FileType2Lexicon.LexiconId = @LexiconId
              AND     [File].ClientIdString = [People].ClientIdString
              AND     CFV.Value &lt;&gt; ''
              AND     (CF2L.Label = 'Date of Arrest')
          SELECT *
          FROM RankedCTE
          WHERE RowNumber = 1
  </code></pre>