On Mon, Jan 25, 2016 at 9:32 PM, Larry Martell <larry.mart...@gmail.com> wrote:
> On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers <joh...@pixelated.net> > wrote: > > You should probably turn this into a UNION and put an index on each > column: > > > > SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom = > > 'E-CD7' > > UNION ALL > > SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom = > > 'E-CD7' > > UNION ALL > > SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE > > f_tag_bottom_minor_axis = 'E-CD7' > > UNION ALL > > SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE > > f_tag_bottom_major_axis = 'E-CD7' > > UNION ALL > > SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7' > > ; > > This may work for me, but I need to explore this more tomorrow. I need > the select to control the rows included in the aggregation. For > example, the rows where the f_tag_* col that is used does not = > 'E-CD7' should not be included in the aggregation. Also, I grossly > simplified the query for this post. In reality I have 15 items in the > where clause and a having as well. > I see, I missed the STDDEV() function you had, perhaps you could add that column to each SELECT in the untion, then wrap the entire union inside another select: SELECT ftag, STDDEV(ch_x_top) FROM ( ..union stuff here... ) > > > Doing this any other way will prevent index usage and a full table scan > will > > be required. > > Yes, I will be adding indices - right now I am just worried about > getting the query to work. But this is important as it's a big table. > So I do appreciate you mentioning it. > In reality, you could repeat the IFNULL(...) in the where clause the same way you have it in the column list. Not the optimal solution but it'd work for a proof of concept. > > > Is there a possibility of more than one column matching? How would you > > handle that? > > I was told only 1 of the 5 will be populated and the other 4 will be > null. But still, I said I have to code for the case where that is not > true. So then I was told to use the first one I find that is not null, > looking in the order I had in my original post. > You could also wrap another select around the union to handle more than one of the columns having a value. You could use the outer select to pick the one you wanted, something similar to: SELECT IFNULL(f_tag_ch_y_bottom,IFULL(f_tag_ch_x_bottom,IFNULL(....,STDEV(ch_x_top) FROM ( SELECT f_tag_ch_y_bottom,NULL as f_tag_ch_x_bottom,NULL AS f_tag_bottom_minor,..., ch_x_top FROM data_cst WHERE f_tag_ch_y_bottom = 'E-CD7' UNION ALL SELECT NULL AS f_tag_ch_y_bottom, f_tag_ch_x_bottom,NULL AS f_tag_bottom_minor,...,ch_x_top FROM data_cst WHERE f_tag_ch_x_bottom = 'E-CD7' UNION.... ) And so on for each column/query. > > > > On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell <larry.mart...@gmail.com> > > wrote: > >> > >> I know I cannot use an alias in a where clause, but I am trying to > >> figure out how to achieve what I need. > >> > >> If I could have an alias in a where clause my sql would look like this: > >> > >> SELECT IFNULL(f_tag_bottom, > >> IFNULL(f_tag_bottom_major_axis, > >> IFNULL(f_tag_bottom_minor_axis, > >> IFNULL(f_tag_ch_x_bottom, > >> IFNULL(f_tag_ch_y_bottom, NULL))))) as ftag, > >> STDDEV(ch_x_top) > >> FROM data_cst > >> WHERE ftag = 'E-CD7' > >> GROUP BY wafer_id, lot_id > >> > >> But I can't use ftag in the where clause. I can't put it in a having > >> clause, as that would exclude the already aggregated rows and I want > >> to filter then before the aggregation. Anyone have any idea how I can > >> do this? > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/mysql > >> > > > > > > > > -- > > ----------------------------- > > Johnny Withers > > 601.209.4985 > > joh...@pixelated.net > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
