title: If a table has an unindexed column with a 1 to many relationship to an indexed column, how to optimize a query for the unindexed column?
body: <p>If there is a two column table MyTable with enough records that optimization of queries is relevant.</p>
  
  <p><strong>CorporationID</strong> int (unindexed)</p>
  
  <p><strong>BatchID</strong> int (indexed)</p>
  
  <p>And lets assume there is always a 1 to many relationship between CorporationID and BatchID. In other words for each BatchID there will be only one CorporationID, but for each CorporationID there will be many BatchID values.</p>
  
  <p>We need to get all BatchID values where corporationID = 1.</p>
  
  <p>I know the simplest solution may be to just add an index to CorporationID, but assuming that is not allowed, is there some other way to inform SQL that each BatchID corresponds to only 1 CorporationID, through a query or otherwise?</p>
  
  <pre><code>select distinct batchid from MyTable where corporationID = 1
  </code></pre>
  
  <p>It seems this is not effective.</p>
  
  <pre><code>select batchid from (select min(corporationid) corporationid, batchid 
  from MyTable group by batchid) subselect where corporationid = 1 
  </code></pre>
  
  <p>This is also not effective, I assume due to SQL needing to iterate needlessly through all values of corporationid? (Does an aggregate function exist to select any() value which would not have the overhead of min(), max(), sum() or avg()??)</p>
  
  <pre><code>select batchid 
  from (
         select corporationid, batchid 
         from (
                 select *, ROW_NUMBER() OVER (PARTITION BY batchid ORDER BY(SELECT NULL)) AS RowNumber
                from mytable
              ) subselect 
         where RowNumber = 1
        ) subselect2 
  where corporationid = 1 
  </code></pre>
  
  <p>Would this work? By arbitrarily selecting the corporationid related to row number 1 after partitioning by batchid with no order?</p>