Filters with Subqueries

Renato Marcello dos Reis shared this question 3 years ago
Answered

Hi guys,

I hope everything is fine with all of you.

I'm having a client who asked me something that I can do easily in SQL Query, but I couldn't find a way to do the same thing in Yellowfin.

To explain, I'm gonna put a query below, so I can explain what I'm trying to achieve.

SELECT 
	A.PRIMARY_CODE,
	A.SALES_DATE,
	A.PRODUCT 
FROM 
	SALES A
WHERE 
	SALES_DATE = (SELECT MAX(SALES_DATE) FROM SALES B WHERE A.PRIMARY_CODE = B.PRIMARY_CODE)
GROUP BY
	A.PRIMARY_CODE, A.SALES_DATE, A.PRODUCT 

The above code has a simple meaning. The client has sales from products more than once, and the query should give us only the most recent one. We just need to show the top 1 sale from which the date is the newer one.

I tried to achieve this by using the custom query over a filter, but the subquery cannot have a relation to the report columns, so I can't do some "WHERE A.PRIMARY_CODE = B.PRIMARY_CODE".

Is there a simple way to do this in Yellowfin?

Thanks in advance.

Regards,

Renato Marcello

Replies (0)

Leave a Comment
 
Attach a file