Thread: How To Exclude True Values
hi all, how can i exclude true values for this query? http://www.rafb.net/paste/results/obtkGz26.html if i uncomment out -- AND t_inspect_result.inspect_result_pass = 'f' it looks for prior falses within an inspect_id and returns it. i want the original result set minus the trues, if possible. tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> hi all, > > how can i exclude true values for this query? > > http://www.rafb.net/paste/results/obtkGz26.html > > if i uncomment out > > -- AND t_inspect_result.inspect_result_pass = 'f' > > it looks for prior falses within an inspect_id and > returns it. i want the original result set minus > the > trues, if possible. > > tia... this SQL appears to do the trick... http://www.rafb.net/paste/results/zZKIjH80.html __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > hi all, > > > > how can i exclude true values for this query? > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > if i uncomment out > > > > -- AND t_inspect_result.inspect_result_pass = > 'f' > > > > it looks for prior falses within an inspect_id and > > returns it. i want the original result set minus > > the > > trues, if possible. > > > > tia... > > this SQL appears to do the trick... > > http://www.rafb.net/paste/results/zZKIjH80.html > the finished query can be found here: http://www.rafb.net/paste/results/Cu2FoO56.html working out that query sure felt an awful lot life fighting a fire breathing dragon... with tissue paper armour... -lol- __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > how can i exclude true values for this query? > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > if i uncomment out > > > > -- AND t_inspect_result.inspect_result_pass = 'f' > > > > it looks for prior falses within an inspect_id and > > returns it. i want the original result set minus > > the > > trues, if possible. > > > > tia... > > this SQL appears to do the trick... > > http://www.rafb.net/paste/results/zZKIjH80.html I have one idea that hopefully wont complicate you query but it could simplfy your query by getting rid of the query nexting. Also, I haven't tested it. Basically, Replace the DISTINCT ON (t_inspect.inspect_id) construct with GROUP BY t_inspect.inspect_id HAVING t_inspect_result.inspect_result_pass = 'f' Regards, Richard Broersma Jr.
> > > how can i exclude true values for this query? > > > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > > > if i uncomment out > > > > > > -- AND t_inspect_result.inspect_result_pass = > 'f' > > > > > > it looks for prior falses within an inspect_id > and > > > returns it. i want the original result set > minus > > > the > > > trues, if possible. > > > > > > tia... > > > > this SQL appears to do the trick... > > > > http://www.rafb.net/paste/results/zZKIjH80.html > > I have one idea that hopefully wont complicate you > query but it could simplfy your query by > getting rid of the query nexting. Also, I haven't > tested it. > > Basically, Replace the > DISTINCT ON (t_inspect.inspect_id) > > construct with > > GROUP BY t_inspect.inspect_id > HAVING t_inspect_result.inspect_result_pass = 'f' Richard, thanks for taking the time to review the query. i think this is what you meant... http://www.rafb.net/paste/results/Wzxbxw69.html do correct it if i misunderstood or if you see something that will get me the intended result. this result doesn't give me what i want. here are the tables... t_inspect inspect_id ... t_inspect_result inspect_id (fkey to t_inspect) inspect_result_pass (bool) inspect_result_timestamp ... here's some sample data... inspect_id, inspect_result_id, inspect_result_pass, inspect_result_timestamp 1, 1, f, 2006-06-05 01:00:00 1, 2, f, 2006-06-05 02:00:00 1, 3, t, 2006-06-05 03:00:00 2, 4, t, 2006-06-05 04:00:00 3, 5, f, 2006-06-05 05:00:00 3, 6, t, 2006-06-05 06:00:00 4, 7, f, 2006-06-05 07:00:00 *4, 8, f, 2006-06-05 08:00:00* the original query will return a single result - the result within the "*" markers. i wany all the inspect_ids where the last (time wise) related inspect_result_pass is a false. if the last value isn't false, i don't want it at all. the query linked in this post will return 3 lines... 1, 2, f 3, 5, f *4, 8, f* -- the only one i really want instead of totally excluding latest time inspect_result_pass true values, grouped by inspect_id (what i need), it checks for prior false values and, if they exist, it returns the prior false value row. if you know of a way to update the above linked query to exclude those rows where the latest time value of inspect_result_pass is pass, grouped by inpect_id, be sure to let me know. thanks again for taking the time to review the code and provide input. while i'm no sql expert, this particular query was the most challenging one i've run into so far. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> inspect_id, inspect_result_id, inspect_result_pass, > inspect_result_timestamp > 3, 5, f, 2006-06-05 05:00:00 > 3, 6, t, 2006-06-05 06:00:00 > 4, 7, f, 2006-06-05 07:00:00 > *4, 8, f, 2006-06-05 08:00:00* > the query linked in this post will return 3 lines... > > 1, 2, f > 3, 5, f > *4, 8, f* -- the only one i really want From your sample it seems to me that you are really only looking for the most recient occuring record that have produced a false test regardless of which Inspect_id or inspect_result_id it came from. Is this correct? Regards, Richard Broersma Jr.
> > inspect_id, inspect_result_id, > inspect_result_pass, > > inspect_result_timestamp > > 3, 5, f, 2006-06-05 05:00:00 > > 3, 6, t, 2006-06-05 06:00:00 > > 4, 7, f, 2006-06-05 07:00:00 > > *4, 8, f, 2006-06-05 08:00:00* > > the query linked in this post will return 3 > lines... > > > > 1, 2, f > > 3, 5, f > > *4, 8, f* -- the only one i really want > > > From your sample it seems to me that you are really > only looking for the most recient occuring > record that have produced a false test regardless of > which Inspect_id or inspect_result_id it came > from. Is this correct? Richard, not quite. i have an inspection entry form and a defect entry form. an inspection node (each row in t_inspect is an inspection node) that has passed can't have a new defect added - since it has already passed. therefore, in the defect entry form, i only want to display those inspection nodes that don't have a true value. by definition, a true value will always be the last (by time) inspect_result. therefore, i need all the inspect nodes that don't yet have a true value (iow, a true value in the last (by time) inspect_result_pass row). an inspection node can have multiple inspection results, hence, the t_inspection_results table. this might seem counter-intuitive at first, but it makes sense since it may take 5 tries to eventually pass a particular inspection node (f, f, f, f, t) for fucntional test, for example. one node, five tests to pass it. i went to this model b/c a unit can actually pass through the various test nodes multiple times. a unit might go through the same QA node three times - and i need to differentiate between these three QA nodes. if each inspection node only occurred once, i could use one table and link it a t_inspect_node table. due to the multiple passes, though, i can't do this in this case. i hope this makes sense. i'm living it and it is sometimes hard to keep straight. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> an inspection node (each row in t_inspect is an > inspection node) that has passed can't have a new > defect added - since it has already passed. > > therefore, in the defect entry form, i only want to > display those inspection nodes that don't have a true > value. by definition, a true value will always be the > last (by time) inspect_result. > > therefore, i need all the inspect nodes that don't yet > have a true value (iow, a true value in the last (by > time) inspect_result_pass row). > > an inspection node can have multiple inspection > results, hence, the t_inspection_results table. > > this might seem counter-intuitive at first, but it > makes sense since it may take 5 tries to eventually > pass a particular inspection node (f, f, f, f, t) for > fucntional test, for example. one node, five tests to > pass it. here is a test I did. bye the way, I did this is access as it is the only source available to me at the moment. table = test id_i ir_id test stamp 1 1 No 6/5/2006 1:00:00 AM 1 2 No 6/5/2006 2:00:00 AM 1 3 Yes 6/5/2006 4:00:00 AM 2 4 Yes 6/5/2006 4:00:00 AM 3 5 No 6/5/2006 5:00:00 AM 3 6 Yes 6/5/2006 6:00:00 AM 4 7 No 6/5/2006 7:00:00 AM 4 8 No 6/5/2006 8:00:00 AM query -- SELECT a.id_i, a.ir_id, a.test, max(a.stamp) as mstamp -- PostgreSQL might not require an aggregate with the group by. FROM test a INNER JOIN ( SELECT Max(stamp) AS tmax, id_i FROM test group by id_i ) b ON a.stamp = b.tmax group by a.id_i, a.test, a.ir_id, a.stamp having a.test = false ORDER BY a.id_i,a.ir_id, a.test ; results -- id_i ir_id test mstamp 4 8 No 6/5/2006 8:00:00 AM Regards, Richard Broersma Jr.
> > an inspection node (each row in t_inspect is an > > inspection node) that has passed can't have a new > > defect added - since it has already passed. > > > > therefore, in the defect entry form, i only want > to > > display those inspection nodes that don't have a > true > > value. by definition, a true value will always be > the > > last (by time) inspect_result. > > > > therefore, i need all the inspect nodes that don't > yet > > have a true value (iow, a true value in the last > (by > > time) inspect_result_pass row). > > > > an inspection node can have multiple inspection > > results, hence, the t_inspection_results table. > > > > this might seem counter-intuitive at first, but it > > makes sense since it may take 5 tries to > eventually > > pass a particular inspection node (f, f, f, f, t) > for > > fucntional test, for example. one node, five > tests to > > pass it. > > here is a test I did. bye the way, I did this is > access as it is the only source available to me > at the moment. > > table = test > id_i ir_id test stamp > 1 1 No 6/5/2006 1:00:00 AM > 1 2 No 6/5/2006 2:00:00 AM > 1 3 Yes 6/5/2006 4:00:00 AM > 2 4 Yes 6/5/2006 4:00:00 AM > 3 5 No 6/5/2006 5:00:00 AM > 3 6 Yes 6/5/2006 6:00:00 AM > 4 7 No 6/5/2006 7:00:00 AM > 4 8 No 6/5/2006 8:00:00 AM > > > query -- > > SELECT a.id_i, a.ir_id, a.test, max(a.stamp) as > mstamp -- PostgreSQL might not require an > aggregate with the group by. > > FROM test a INNER JOIN > ( > SELECT Max(stamp) AS tmax, id_i > FROM test > group by id_i > ) b > > ON a.stamp = b.tmax > > group by a.id_i, a.test, a.ir_id, a.stamp > > having a.test = false > > ORDER BY a.id_i,a.ir_id, a.test > > ; > > results -- > > id_i ir_id test mstamp > 4 8 No 6/5/2006 8:00:00 AM Richard, that is the result i would need given that data set. i have to digest this version, though. should this query be more efficient than the subquery version as the table starts to get large? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> Richard, that is the result i would need given that > data set. i have to digest this version, though. > > should this query be more efficient than the subquery > version as the table starts to get large? My experience is that Distinct On queries do not preform as well as their group by counter parts. I believe that others have also commented to the same effect. To speed the query you could apply indexes on the group by fields and the join columns. Also, I produced a second query using PostgreSQL: select a.id_i, a.ir_id, a.test, a.stamp from test a join ( select max(stamp) as mstamp, id_i from test group by id_i ) b on a.stamp = b.mstamp where a.test = false ; -- result id_i | ir_id | test | stamp ------+-------+------+--------------------- 4 | 8 | f | 2006-06-05 08:00:00
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > Richard, that is the result i would need given > that > > data set. i have to digest this version, though. > > > > should this query be more efficient than the > subquery > > version as the table starts to get large? > > My experience is that Distinct On queries do not > preform as well as their group by counter parts. > I believe that others have also commented to the > same effect. > > To speed the query you could apply indexes on the > group by fields and the join columns. > > Also, I produced a second query using PostgreSQL: > > select a.id_i, a.ir_id, a.test, a.stamp > from test a > > join > ( > select max(stamp) as mstamp, id_i > from test > group by id_i > ) b > > on a.stamp = b.mstamp > > where a.test = false > ; > > -- result > > id_i | ir_id | test | stamp > ------+-------+------+--------------------- > 4 | 8 | f | 2006-06-05 08:00:00 Richard, given the following table structure... t_inspect ========= inspect_id ... t_inspect_result ================ inspect_result_id inspect_id (fkey) inspect_result_pass (bool) -- indicates fail or pass inspect_result_timestamp the following query... select a.inspect_id, a.inspect_result_id, a.inspect_result_pass, a.inspect_result_timestamp from t_inspect_result a join ( select max(t_inspect_result.inspect_result_timestamp) as mstamp, t_inspect_result.inspect_id from t_inspect_result group by t_inspect_result.inspect_id ) b on a.inspect_result_timestamp = b.mstamp yields the following error: ERROR: schema "a" does not exist i tried to interpret you query and apply it to my case, but, apparently, i didn't too good of a job. do you see the error? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> yields the following error: > > ERROR: schema "a" does not exist > > i tried to interpret you query and apply it to my > case, but, apparently, i didn't too good of a job. > > do you see the error? Hmmm... That is strange. but according to the PostgreSQL documentation on select you can add the option [AS] keyword between the table_name and alias. http://www.postgresql.org/docs/8.1/interactive/sql-select.html [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias[, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] select a.inspect_id, a.inspect_result_id, a.inspect_result_pass, a.inspect_result_timestamp from t_inspect_result AS a join ( select max(t_inspect_result.inspect_result_timestamp) as mstamp, t_inspect_result.inspect_id from t_inspect_result group by t_inspect_result.inspect_id ) AS b on a.inspect_result_timestamp = b.mstamp ; Regards, Richard Broersma Jr.
> > yields the following error: > > > > ERROR: schema "a" does not exist > > > > i tried to interpret you query and apply it to my > > case, but, apparently, i didn't too good of a job. > > > > do you see the error? > > Hmmm... That is strange. but according to the > PostgreSQL documentation on select you can add the > option [AS] keyword between the table_name and > alias. > > http://www.postgresql.org/docs/8.1/interactive/sql-select.html > [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( > column_alias [, ...] ) ] ] > > ( select ) [ AS ] alias [ ( column_alias [, ...] > ) ] > > select a.inspect_id, a.inspect_result_id, > a.inspect_result_pass, > a.inspect_result_timestamp > from t_inspect_result AS a > > join > ( > select > max(t_inspect_result.inspect_result_timestamp) > as mstamp, > t_inspect_result.inspect_id > from t_inspect_result > group by t_inspect_result.inspect_id > ) AS b > > on a.inspect_result_timestamp = b.mstamp > ; Richard, i got the expected rewsults! now i just have to study the query to figure out what it does. -lol- i tried using AS, but i only did it in one place - doh! the error message just moved to the next place i didn't do it. i'll know better next time. thanks for the help. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> Richard, > > i got the expected rewsults! now i just have to study > the query to figure out what it does. -lol- > > i tried using AS, but i only did it in one place - > doh! the error message just moved to the next place i > didn't do it. i'll know better next time. > > thanks for the help. I am surprised that the query did not work the first time without the optional AS keyword. I would be interested in knowing why your server requires the AS and mine doesn't. Regards, Richard Broersma Jr.
> > Richard, > > > > i got the expected rewsults! now i just have to > study > > the query to figure out what it does. -lol- > > > > i tried using AS, but i only did it in one place - > > doh! the error message just moved to the next > place i > > didn't do it. i'll know better next time. > > > > thanks for the help. > > I am surprised that the query did not work the first > time without the optional AS keyword. I > would be interested in knowing why your server > requires the AS and mine doesn't. my dev box is winxp home with pgsql 8.1.3 (i think it is .3, anyway). if platform issues don't expain it, then i don't know what it is. i think i've figured out the gist of the thought process behind the SQL query... 1. select everything from t_inspect_result as table a 2. select max timestamp value entries in t_inspect_result as table b 3. choose only choose those rows where the max timestamps of table a and b are equal. is that about it? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> i think i've figured out the gist of the thought > process behind the SQL query... > > 1. select everything from t_inspect_result as table a > 2. select max timestamp value entries in > t_inspect_result as table b > 3. choose only choose those rows where the max > timestamps of table a and b are equal. > > is that about it? The one problem with query would be if there is a possibility that two differnt tests will have the exact same time stamp. If that is a possibility, then you could use the exact same query structure but replace Max(timestamp) with max(inspect_result_id) and then join on inspect_result_id instead. Regards, Richard Broersma
> Also, I produced a second query using PostgreSQL: > select a.id_i, a.ir_id, a.test, a.stamp > from test a > join > ( > select max(stamp) as mstamp, id_i > from test > group by id_i > ) b > on a.stamp = b.mstamp > where a.test = false > ; > -- result > id_i | ir_id | test | stamp > ------+-------+------+--------------------- > 4 | 8 | f | 2006-06-05 08:00:00 I found this query produced the same result. It is a list slower than the first with my small dataset. but maybe it will improve for larger datasets? select t1.id_i, t1.ir_id, t1.test, t1.stamp, t1.inttest from test as t1 where t1.stamp = ( select max(T2.stamp) from test as t2 where t2.id_i = t1.id_i) and t1.test = 'f'; Regards, Richard Broersma Jr.