Hi All, I am trying to get the minimum value on one particular dataset. and I am facing an issue where in SQL query giving proper results and druid native query is not giving the expected results after applying granularity to the druid query . For the dataset refer to dataset.csv for its columns and types image.png here I am trying to get the minimum value in the doubleValue column where the quality is good and performing deduplication. this is an equivalent SQL query that is giving proper results with cte as (SELECT __time, itemName, LATEST_BY(doubleValue, TIME_PARSE(incomingEventhubTimeStamp)) AS doubleValue, LATEST_BY(quality, TIME_PARSE(incomingEventhubTimeStamp),100) AS quality from "min-data" where itemName in ('test','test1') and quality ='Good' and __time BETWEEN '2022-11-30T183000.000Z' and '2022-11-30T223000.000Z' GROUP BY itemName,__time) --select __time,itemName,doubleValue, quality --from cte as main where (itemName,doubleValue) in (select itemName, min(doubleValue) from cte GROUP BY itemName) select leftside.__time,rightside.itemName,rightside.doubleValue from cte as leftside inner join (select itemName, min(doubleValue) as doubleValue from cte GROUP BY itemName) as rightside ON leftside.doubleValue=rightside.doubleValue where leftside.itemName = rightside.itemName when we convert this SQL query into Druid native query and apply granularity .for druid native refer druid native query.txt.query is giving wrong results. can anyone help here?

Hi Pavithra, I haven't taken a look at your data or native query text, but just looking at the SQL ... ... one thing odd that I noticed is that your final "where l.itemName = r.itemName" I would phrase as the second part of the join condition using an AND. Unfortunately I don't know native query syntax well enough to know if this would make a difference. Also, your __time value, I assume it is truncated to a high enough level that you are looking for data with the latest incomingEvenhubTimeStamp within each time "slice"?

@Pavithra k I'd suggest running

EXPLAIN PLAN FOR

on the SQL query, seeing how it got translated to native, and comparing that against your native query