You can translate the question and the replies:

SELECT DISTINCT Doesn't work in subquery

Hello, Whenever I try running a subquery with SELECT DISTINCT I get the following error message: > Query Execution failed > Reason: SQL ERROR -50006 [HY000]: Error executing view: Functions min, rank are not executable I'm not sure why this is happening. I can run the query on it's own. For Example: This works: * SELECT DISTINCT ID FROM table* This Doesn't work: ... * left join ( SELECT DISTINCT ID FROM table) table 2 on table1.ID=table2.ID*
13-02-2017 10:39:25 -0500

1 Answer

Hi, The error code which you shared is not related to distinct clause, It might be related to aggregation function if you have used any in the first part of the query. ….left join ( SELECT DISTINCT ID FROM table) table 2 on table1.ID=table2.ID To brief you on the use of distinct clause in subquery below is the sample illustration, I tried to join two tables with one table as a actual table and other table as subquery result. I used distinct clause to extract distinct records in the subquery and then joined with actual table. It worked fine for me without any errors. For example, I have Employee table with employee details and employeeid as primary key. EmpId Name Salary 1 XXX 10 2 YYY 20 3 ZZZ 30 4 VVV 40 I have Sales table with sales details of each employee and saleid as primary key. Saleid Empid Prodid 1 1 10 2 1 10 3 2 11 4 2 12 5 3 15 6 3 15 Now I want to know the salary of only employees who made sales. For this, I executed the below query by using distinct clause in subquery and it worked for me, "Select a.empId, a.salary from employee a inner join (select distinct empid from sales) b on a.empId = b.empId" Actual Result, Empid Salary 1 10 2 20 3 30 Hope this helps!!
Denodo Team
14-02-2017 06:51:00 -0500
You must sign in to add an answer. If you do not have an account, you can register here