title: SPARK SQL select from group by select result
body: <p>I have a table which is called table_new.
  In the first step I would like to group by the results by id, kmstand, vacationname and vacationvalue where only one count exists for each of the group by. For this step, I have already created a query:</p>
  
  <pre><code>SELECT id, kmstand, vacationame, vacationvalue 
   FROM `db_1`.`table_new` 
   WHERE (vacationame='vacation1' 
      OR vacationame='vacation2' 
      OR vacationame='vacation3' 
      OR vacationame='vacation4') 
   GROUP BY id, kmstand, vacationame, vacationvalue 
   HAVING COUNT(*) = 1 ORDER BY id, kmstand DESC
  </code></pre>
  
  <p>The result is:</p>
  
  <pre><code>    id  kmstand vacationame vacationvalue
  1   1   4000    vacation1   munich
  2   1   4000    vacation1   stuttgart
  3   1   5500    vacation4   koln
  4   1   5500    vacation2   frankfurt
  5   1   5500    vacation3   berlin
  6   1   5500    vacation1   potsdam
  7   2   6000    vacation2   new york
  8   2   6000    vacation1   bangladesh
  9   2   3000    vacation1   washington
  10  2   3000    vacation3   chicago
  </code></pre>
  
  <p>Now, I would like to select the id where the combination kmstand and vacationname is now distinct. This means the result should be:</p>
  
  <pre><code>        id  kmstand vacationame vacationvalue
  1   1   5500    vacation4   koln
  2   1   5500    vacation2   frankfurt
  3   1   5500    vacation3   berlin
  4   1   5500    vacation1   potsdam
  5   2   6000    vacation2   new york
  6   2   6000    vacation1   bangladesh
  7   2   3000    vacation1   washington
  8   2   3000    vacation3   chicago
  </code></pre>
  
  <p>For this, I have created the following nested sql query:</p>
  
  <pre><code>    SELECT id, kmstand, count(*) as cnt 
  FROM `db_1`.`table_new`
  WHERE (SELECT id, kmstand, vacationame, vacationvalue 
       FROM `db_1`.`table_new` 
       WHERE (vacationame='vacation1' 
          OR vacationame='vacation2' 
          OR vacationame='vacation3' 
          OR vacationame='vacation4') 
       GROUP BY id, kmstand, vacationame, vacationvalue 
       HAVING COUNT(*) = 1 ORDER BY id, kmstand DESC) 
  GROUP BY id, kmstand 
  HAVING cnt = 1 
  ORDER BY id, kmstand DESC
  </code></pre>
  
  <p>I tried it also without the where clause or without from and didn't find a solution. For this SQL query I get the following error message: <code>org.apache.spark.sql.AnalysisException: cannot recognize input near 'SELECT' 'id' ',' in expression specification; line 3 pos 7</code>
  I am pretty sure, that the syntax is not right. Do you have any suggestions?</p>