"Error while processing statement" running a query finding unique values with multiple values in other rows Is there anything wrong in the syntax of this query finding unique values with multiple values in other rows?
Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr As Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) &gt; 1
      ) As Y
On Ss.`cd` = Y.`cd`

I am getting this error:

[Cloudera]HiveJDBCDriver ERROR processing query/statement.
Error Code: 2, SQL state: Error while processing statement: FAILED:
Execution Error, return code 2 from
org.apache.hadoop.hive.ql.exec.mr.MapRedTask, Query:  .. my query
I suspect you are using a Hive version that is before 0.13.0. In that case the problem is the AS keyword when giving alias to subquery.

From HiveQL documentation Subqueries in the FROM Clause:


SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...   (Note: Only valid starting with Hive 0.13.0)

  
  ...
  The optional keyword "AS" can be included before the subquery name in Hive 0.13.0 and later versions (HIVE-6519).


Therefore, valid syntax would be:

Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr AS Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) &gt; 1
      ) Y                                  -- AS removed
On Ss.`cd` = Y.`cd` ;




Regarding the semantics of the query. I'm not sure what the actual problem is but this query is guaranteed to return 0 rows, no matter what the data in the table are.

COUNT(DISTINCT cd) will be 1 when you have GROUP BY cd so the HAVING clause will be FALSE always and the Y subquery will have 0 rows.