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. > 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. > 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. > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
