title: Select DISTINCT on JPA
body: <p>I have a table with the <a href="https://en.wikipedia.org/wiki/ISO_4217" rel="nofollow noreferrer">ISO 4217 values</a> for currencies (With 6 rows, ID, Country, Currency_Name, Alphabetic_code, Numeric_Code, Minor_Unit).</p>
  
  <p>I need to get some of the data for the <a href="https://en.wikipedia.org/wiki/Template:Most_traded_currencies" rel="nofollow noreferrer">4 most used currencies</a>, and my "pure" SQL query goes like this:</p>
  
  <pre><code>select distinct currency_name, alphabetic_code, numeric_code 
  from currency 
  where ALPHABETIC_CODE IN ('USD','EUR','JPY','GBP') 
  order by currency_name;
  </code></pre>
  
  <p>Which returns a 4-row table with the data I need. So far, so good. </p>
  
  <p>Now I have to translate this to our JPA xml file, and the problems begin. The query I'm trying to get is like this:</p>
  
  <pre><code>SELECT DISTINCT c.currencyName, c.alphabeticCode, c.numericCode
  FROM Currency c 
  WHERE c.alphabeticCode IN ('EUR','GBP','USD','JPY') 
  ORDER BY c.currencyName
  </code></pre>
  
  <p>This returns a list with one row for each country that has some of those currencies (As if there were no "DISTINCT" on the query). And I'm scratching my head on why. So the questions would be:</p>
  
  <p>1) How can I make this query to return what the "pure" SQL is giving me? </p>
  
  <p>2) Why is this query seemingly ignoring my "DISTINCT" clause? There's something I'm missing here, and I don't get what. What's going on, what I'm not getting? </p>
  
  <p>EDIT: Well, this is getting weirder. Somehow, that JPA query works as intended (Returning 4 rows). I've tried this (As I needed some more info):</p>
  
  <pre><code>SELECT DISTINCT c.currencyName, c.alphabeticCode, c.numericCode, c.minorUnit, c.id
  FROM Currency c 
  WHERE c.alphabeticCode IN ('EUR','GBP','USD','JPY') 
  ORDER BY c.currencyName
  </code></pre>
  
  <p>And it seems the ID is messing everything, as removing it from the query goes back to return the 4-row table. And putting parenthesis is useless.</p>
  
  <p>btw, we are using eclipse link.</p>