title: SQL Server using CROSS APPLY to avoid duplicate records in SELECT statement
body: <p>I have a table where there is no primary key or any unique column(No any auto increment column).</p>
  
  <p>And I have to select records from that table avoiding duplicate records. I have used <code>ROW_NUMBER()</code> function for that and it's working for me.</p>
  
  <p>Here is the working code</p>
  
  <pre><code>     WITH Result AS
          (
          SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY 
                                            CategoryName
                                           ,ProductGroup
                                           ,Brand
                                           ,Division 
                                           ,ProductNo                                                                                                                     
                                           ,LocationNo                                                                                         
                                           ,Season                                                                                                
                                           ,Year                                                                                          
                                           ,Month                                                                                         
                                           ,Week                                                                                          
                                           ,Day                                                                                           
                                           ,DemandQty
                                        ), 0) AS RN, * 
          FROM TABLE_1
     )
  SELECT * 
  FROM Result AS D
  
  WHERE RN IN
   (
      SELECT MAX(RN) MAX_RN
      FROM Result 
      GROUP BY
     CategoryName
    ,ProductGroup
    ,Brand
    ,Division 
    ,ProductNo                                                                                                                    
    ,LocationNo                                                                                        
    ,Season                                                                                               
    ,Year                                                                                         
    ,Month                                                                                            
    ,Week                                                                                         
    ,Day                                                                                          
    ,DemandQty
  )   
  </code></pre>
  
  <p>This gives the result by avoiding duplicate records.</p>
  
  <p>What I need to do is without writing this <code>WITH</code> clause I need to write it using <code>CROSS APPLY</code> because it'll take too much time to select records when there are about <strong>500000</strong> records with the Row Number</p>
  
  <p>So far I tried to re-write the query just like below to avoid duplicate records.</p>
  
  <pre><code>SELECT * FROM 
  TABLE_1 d
  CROSS APPLY (
  SELECT TOP 1 * FROM
  TABLE_1 x
  WHERE x.[Season] = d.Season
  AND ISNULL(x.[Brand]            ,'')= ISNULL(d.[Brand]            ,'')
  AND ISNULL(x.[Division]         ,'')= ISNULL(d.[Division]         ,'')
  AND ISNULL(x.[CategoryName]     ,'')= ISNULL(d.[CategoryName]     ,'')
  AND ISNULL(x.[ProductGroup]     ,'')= ISNULL(d.[ProductGroup]     ,'')
  AND ISNULL(x.[ProductNo]        ,'')= ISNULL(d.[ProductNo]        ,'')
  AND ISNULL(x.[LocationNo]       ,'')= ISNULL(d.[LocationNo]       ,'')
  AND ISNULL(x.[Year]             ,'')= ISNULL(d.[Year]             ,'')
  AND ISNULL(x.[Month]            ,'')= ISNULL(d.[Month]            ,'')
  AND ISNULL(x.[Week]             ,'')= ISNULL(d.[Week]             ,'')
  AND ISNULL(x.[Day]              ,'')= ISNULL(d.[Day]              ,'')
  AND ISNULL(x.[DemandQty]        ,'')= ISNULL(d.[DemandQty]        ,'')
  AND ISNULL(x.[DeletionIndicator],'')= ISNULL(d.[DeletionIndicator],'')
  AND ISNULL(x.[CreatedBy]        ,'')= ISNULL(d.[CreatedBy]        ,'')
  AND ISNULL(x.[CreatedOn]        ,'')= ISNULL(d.[CreatedOn]        ,'')
  AND ISNULL(x.[UpdatedBy]        ,'')= ISNULL(d.[UpdatedBy]        ,'')
  AND ISNULL(x.[UpdatedOn]        ,'')= ISNULL(d.[UpdatedOn]        ,'')
  ) y
  </code></pre>
  
  <p>But it's not giving the correct result for me still duplicate records are coming?</p>
  
  <p>Assume that there are 10 identical rows along with other rows, I need to retrieve only one record from those 10 identical records along with other rows that are not duplicated.</p>
  
  <p>Is there any way to do this without using <code>ROW_NUMBER</code> and <code>WITH</code> ?</p>