Subquery fails with "single-row subquery returns more than one row"

[Mark Herrmann]

Hi

we're trying to follow this part of the documentation in order to to filter a data set with multiple items having different time stamps to the items with the latest time stamp per LOTNO, WAFER_NO and PARAMETER_NAME.

I've put the documentation side-by-side with the SQL log from Spotfire server and I can't seem to see a difference. Where's the problem

 

 

Here is how the "special" column looks like vs. the documentation:

 

 

I also found this in the community but it didn't really help.

Thanks,

Mark

[Fabian Duerr]

I think the difference bewteen the example and your case is that tha example returns a single value (the average of an entire column) but you return an entire table. I think if you filter your table (sub query) to the lot and wafer number this could work. But of course you might get multiple hits for this time. So you need the lot, wafer, parameter information as well...

I don't really like these kind of subquerrys because they are hard to read. I prefer to write some WITH clauses instead.So I would write an SQL statementlike this

with maxtime as (

select lotno, wafer_no, parameter_name, max(measure_time) as measure_time from sf_ace_read.sf_metro_summary

group by lotno, wafer_no, parameter_name

)

select s1.lotno, s1.wafer_no, s1..... , from sf_ace_read.sf_metro_summary s1, maxtime mt

where mt.measure_time = s1.measure_time

and mt.lotno = s1.lotno

and mt.wafer_no = s1.wafer_no

and mt.parameter_name = s1.parameter_name