[SQL] Advanced Query
hi all, i posted this problem on the novice thread, but it makes much more sense to post it here, instead. sorry fo rthe double posting, i'll be sure to post advanced SQL questions here in the future. i have the following two tables (trimmed down for simplicity's sake): t_inspect id, inspect_timestamp t_inspect_result id, inspect_id, inspect_pass yes, i need both tables, although it might not be obvious since i trimmed down the columns in this simple example. inspect_pass (bool): pass = true, fail = false let's say i have the following values: t_inspect 1, 2006-05-31... 2, 2006-06-01... t_inspect_result 1, 1, true 2, 2, false 3, 2, false 4, 2, false 5, 2, true iow, the first inspection passes the first time, the second inspection (t_inspect.id = 2) had to be inspected 4 times before it pass inspection. you can assume it was reworked inbetween inspections and more defects were found upon reinspection. i'm trying to develop a query that will provide the first pass yield. iow, the yield generated by counting *only* the results associated with the first time a unit is inspected for a given inspect.id. t_inspect_result 1, 1, *true* -- first inspect for t_inspect.id = 1 2, 2, *false* -- first inspect for t_inspect.id = 2 3, 2, false 4, 2, false 5, 2, true specifically, this case would yield 50% (1 pass / 2 total) since the first inspection passed the first time and the second inspection failed the first time. i think i can get the first pass results through a given inspection by using "distinct on (t_inspect.id)..." i say think b/c the actual query is quite complex and i'm not 100% sure my results are consistent with what i'm expecting. i think i can get the results of the entire t_inspect_result table using the count function - get #passes, get #total and do some math. what i can't seem to do is to get both - a count of the total number of t_inspect_result.inspect_pass where the value is true and a total count, by unique t_inspect.id. any guidance would be much appreciated. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Advanced Query
> On Thu, Jun 01, 2006 at 04:09:21PM -0700, > [EMAIL PROTECTED] wrote: > > what i can't seem to do is to get both - a count > of > > the total number of t_inspect_result.inspect_pass > > where the value is true and a total count, by > unique > > t_inspect.id. > > Are you looking for something like this? > > SELECT 1.0 * sum(CASE WHEN inspect_pass THEN 1 ELSE > 0 END) / count(*) > FROM ( > SELECT DISTINCT ON (inspect_id) inspect_id, > inspect_pass > FROM t_inspect_result > ORDER BY inspect_id, id > ) AS s; > > Multiply by 100.0 instead of 1.0 if you want > percent. > > If you have a cast from boolean to integer (built-in > in 8.1, easily > created in earlier versions) then you could replace > the CASE > expression with a cast (inspect_pass::integer). > Whether to use the > more explicit CASE or the more concise cast is a > matter of style. Michael, wow! i never heard of case or seen the "if / then" style in sql. i need to get out more... or maybe less. ;-) i have been working through a simplified version of the problem and i am accurately getting the "pieces" of data that i need (#pass, #total) - it is similar to your example following your first FROM statement. i just need to work the complexities back in w/o destroying my current results and then perform the math on the results - either in pgsql or in my app. i'll play around with the more advanced stuff tomorrow. thanks - i think i have enough pieces to get this done. if not... "i'll be baaawck." __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Using Query Result in WHERE Clause
hi all, SUMMARY: i want to replace AND t_inspect.inspect_pass = 'f' with a complex query that yields the same result. the complex query is in the form of... SELECT DISTINCT ON (t_inspect.inspect_id) t_inspect_result.inspect_result_pass, t_inspect_result.inspect_result_id FROM t_inspect_result, t_inspect, t_serial_number, t_product, t_job_number, t_link_contract_number_job_number, t_inspect_area WHERE -- lots of noise here ORDER BY t_inspect.inspect_id, inspect_result_timestamp DESC can i do this? if so, how? DETAILED EXAMPLE: i have the following query (please focus on the *key* element, the rest of the query is just noise): SELECT t_product.product_id, t_product.product_number, t_serial_number.serial_number_id, t_serial_number.serial_number, t_inspect_result.inspect_result_id, t_inspect_area.inspect_area || ', ' || t_inspect_result.inspect_result_timestamp::time FROM t_product, t_job_number, t_link_contract_number_job_number, t_serial_number, t_inspect, t_inspect_area, t_inspect_result WHERE t_product.product_id = t_job_number.product_id AND t_job_number.job_number_id = t_link_contract_number_job_number.job_number_id AND t_link_contract_number_job_number.link_contract_number_job_number_id = t_serial_number.link_contract_number_job_number_id AND t_serial_number.serial_number_id = t_inspect.serial_number_id AND t_inspect_area.inspect_area_id = t_inspect.inspect_area_id AND t_inspect.inspect_id = t_inspect_result.inspect_id -- the statement below is *key* AND t_inspect.inspect_pass = 'f' -- the statement above is *key* ORDER BY t_product.product_number ASC, serial_number::integer ASC, t_inspect_result.inspect_result_timestamp DESC the purpose of the query is to a multi-dimensional array to populate a series of linked select boxes. only those sets of values that haven't already passed an inspection are listed, hence the AND t_inspect.inspect_pass = 'f' line. in order to be able to include... AND t_inspect.inspect_pass = 'f' ...i had to repeat data (added inspect_pass column in t_inspect) when that data already exists (the latest date boolean value in t_inspect_result.inspect_result_pass). t_inspect.inspect pass records the current state of a unit in inspection (pass or fail), while t_inspect_result.inspect_result_pass records all inspect results until a pass is achieved (eg, f, f, f, f, f, f, f, f, t). anyway, i want to incorporate a query that finds this last date inspect_pass_result value and replace... AND t_inspect.inspect_pass = 'f' the query that pulls all the latest date values is... SELECT DISTINCT ON (t_inspect.inspect_id) t_inspect_result.inspect_result_pass, t_inspect_result.inspect_result_id FROM t_inspect_result, t_inspect, t_serial_number, t_product, t_job_number, t_link_contract_number_job_number, t_inspect_area WHERE t_inspect_result.inspect_id = t_inspect.inspect_id AND t_inspect.serial_number_id = t_serial_number.serial_number_id AND t_product.product_id = t_job_number.product_id AND t_job_number.job_number_id = t_link_contract_number_job_number.job_number_id AND t_link_contract_number_job_number.link_contract_number_job_number_id = t_serial_number.link_contract_number_job_number_id AND t_serial_number.serial_number_id = t_inspect.serial_number_id AND t_inspect_area.inspect_area_id = t_inspect.inspect_area_id AND t_inspect.inspect_id = t_inspect_result.inspect_id ORDER BY t_inspect.inspect_id, inspect_result_timestamp DESC tia... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 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... 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] 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... > > 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How To Exclude True Values
> > > 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How To Exclude True Values
> > 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Join issue
> Hi, > > I have one to many (from left to right) > relationships among the below tables > in my database > > user -> house -> contract -> contract status >| > > Also, a single house has a single provider and the > provider has multiple > rates inside the provider_rate table in > chronological order. > > I have a query to return the latest contract and > contract status for a > house... What i am trying to do is to get the rate > of electricity for the > latest contract... I am trying to retrieve the > latest provider rate before a > contract is signed. > > (Please see section marked with >> below). > > Here is the latest version of the SQL and it does > not work (see > ce_contract.contract_created) > > SELECT > ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_status > FROM ce_house > LEFT JOIN ce_provider_rate ON > ce_provider_rate.provider_id=ce_house.provider_id > LEFT JOIN ce_contract ON > ce_house.house_id=ce_contract.house_id > LEFT JOIN ce_contract_status ON > ce_contract.contract_id=ce_contract_status.contract_id > WHERE > ce_contract.contract_id IN (SELECT > MAX(ce_contract.contract_id) FROM > ce_contract GROUP BY ce_contract.house_id) > AND > ce_contract_status.contract_status_id IN (SELECT > MAX(ce_contract_status.contract_status_id) FROM > ce_contract_status GROUP BY > ce_contract_status.contract_id) > AND > >> ce_provider_rate.provider_rate_id IN (SELECT > MAX(ce_provider_rate.provider_rate_id) FROM > ce_provider_rate WHERE > ce_provider_rate.provider_rate_created<=ce_contract.contract_created) > AND > ce_house.house_id='1' > > > I would appreciate any insight to help me solve this > issue... Burak, having battled a couple big queries over that last week, my first piece of advice is simplify the query to the area that is giving you the trouble... iow, set up your query such that it *only* pulls the latest contract rate and leave all the noise out (you already know how to do the noise and can add it back in later). the query you want seems very similar to a subquery i recently put together. instead of the latest date contract rate by house, i needed the latest value of a boolean by inspection node. a simplified version of the query i used is here... http://www.rafb.net/paste/results/m322aH47.html of course, you have to adjust for different table relations. the query, as it stands, lists *all* latest result grouped by inspection_id. you'll be adding... AND ce_house.house_id='1' and add associated FROM tables and WHERE equations. you can specify AND ce_house.house_id='1' to limit the results further. this is my best try at the partial query given i can't totally understand your table structure... http://www.rafb.net/paste/results/MlJUrO80.html if i guessed wrong, adjust for your actual table structure. good luck. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How To Exclude True Values
> > 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 teststamp > 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 testmstamp > 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Join issue
> Table structures: > > ce_house > > house_id > provider_id > > ce_contract > > contract_id > house_id > contract_term > contract_created > > ce_contract_status > > contract_status_id > contract_id > contract_status > contract_status_created > > ce_provider > > provider_id > provider_name > > ce_provider_rate > > provider_rate_id > provider_id > provider_rate_amount > provider_rate_created if i'm reading this right, a house can only have one provider, but a house can have multiple contracts. at first glance, i'd think the contracts should be associated with the provider, not the house. if true, you need to update your table structure. is the rate associated with the contract (i'd assume so with limited informationthink so) or the provider (the way you have it set up)? this is how i envision the table structure (granted, i have limited information so i coul dbe way off base)... ce_house house_id ce_provider provider_id ** house_id (fkey) ** provider_name ce_contract contract_id ** provider_id (fkey) ** contract_term contract_created ce_contract_status contract_status_id contract_id (fkey) contract_status contract_status_created ** ce_contract_rate ** contract_rate_id contract_id (fkey) contract_rate_amount contract_rate_created i also assume, based on your structure, that you can have multiple contract statuses for a given contract. if not,you could probably delete that table and just add contract_status and contract_status_created to ce_contract. i'm sorry if i'm way off base, but i'm trying to wrap my head around the table strcuture, but i'm not necessarily familiar with all the business rules that created the structure - so i may be way off base here. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How To Exclude True Values
--- Richard Broersma Jr <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How To Exclude True Values
> > 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How To Exclude True Values
> > 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Refactor Query...
hi all, i'm trying to refactor this query: http://www.rafb.net/paste/results/RnkFWZ46.html i've added notes to make its function a bit more clear. i have trie dto refactor it to get rid of the "distinct on" function, but i've had no luck. due to all the WHERE clauses, i can't figure out how to do an inner join with something like this: 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 thanks in advance... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Refactor Query... SOLVED
--- [EMAIL PROTECTED] wrote: > hi all, > > i'm trying to refactor this query: > > http://www.rafb.net/paste/results/RnkFWZ46.html > > i've added notes to make its function a bit more > clear. i have trie dto refactor it to get rid of > the > "distinct on" function, but i've had no luck. > > due to all the WHERE clauses, i can't figure out how > to do an inner join with something like this: > > 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 > > thanks in advance... these queries (i have them all over my app!) are getting complex and i'm being challenged to keep everything in order. this is the final result: http://www.rafb.net/paste/results/pnvLSs30.html here's a special thanks to all who have helped on this and other recent query problems. thank you! i *rally* appreciate the chared insight and guidance. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] COPY to table with array columns (Longish)
> Not quite... ZKCOST and ZPRECMPL are two completely > different things. They > have no relation except they're both stored in the > SYS table in D3. > > If we put it in a tree: > SYS >| >+- ZKCOST >| \- >| >+- ZPRECMPL >| +- >| +- >| +- >| \- > > or table: > SYS > > +---+-+-+-+-+ > | ZKCOST| | | | > | > | ZPRECMPL | | | | > | > > +---+-+-+-+-+ > > So other than a variable-element array, the only > other way would be to > create a table with a column count equal to or > greater than the maximum > amount of values (call that value 'Y') that any sys > item holds then if a > particular record (eg, ZKCOST) has less values than > Y, fill the rest of the > columns with blanks (as above). > > That's what I've done at the moment, but only for 9 > columns, so anything > over 9 fields will be truncated past and including > field 10: > wbau=# \d sys > Table "public.sys" >Column | Type | Modifiers > +--+--- >a0 | text | not null >a1 | text | >a2 | text | >a3 | text | >a4 | text | >a5 | text | >a6 | text | >a7 | text | >a8 | text | >a9 | text | > Indexes: > "id" PRIMARY KEY, btree (a0) > > a0 = primary key - eg, ZPRECMPL or ZKCOST > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > On Behalf Of Aaron Bono > Sent: Tuesday, 13 June 2006 2:12 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] COPY to table with array columns > (Longish) > > I think two tables should suffice: ZKCOST and > ZPRECMPL. > > So you would have > > ZKCOST > zkcost_id, > zkcost_value > > and > > ZPRECMPL > zkcost_id, > zprecmpl_id, > zprecmpl_value > > where zkcost_id is the primary key for ZKCOST and > zkcost_id, > zprecmpl_id together are the primary key for > ZPRECMPL and zkcost_id is > a foreign key from ZPRECMPL to ZKCOST. > > That will work won't it? > > -Aaron > > On 6/12/06, Phillip Smith > <[EMAIL PROTECTED]> wrote: > > > So you're suggesting creating a child table for > each SYS record? Ie, a > table called "ZPRECMPL" etc? if the data is unrelated, then the data should be separated (in a perfect world). can you convert into the following form: TABLE_ZKCOST zkcost_id zkcost_value TABLE_ZPRECMPL zprecmpl_id TABLE_ZPRECMPL_DATA zprecmpl_data_id zprecmpl_id zprecmpl_value __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] COPY to table with array columns (Longish)
> > Not quite... ZKCOST and ZPRECMPL are two > completely > > different things. They > > have no relation except they're both stored in the > > SYS table in D3. > > > > If we put it in a tree: > > SYS > > | > > +- ZKCOST > > | \- > > | > > +- ZPRECMPL > > | +- > > | +- > > | +- > > | \- > > > > or table: > > SYS > > > > > +---+-+-+-+-+ > > | ZKCOST| | | | > > > | > > | ZPRECMPL | | | | > > > | > > > > > +---+-+-+-+-+ > > > > So other than a variable-element array, the only > > other way would be to > > create a table with a column count equal to or > > greater than the maximum > > amount of values (call that value 'Y') that any > sys > > item holds then if a > > particular record (eg, ZKCOST) has less values > than > > Y, fill the rest of the > > columns with blanks (as above). > > > > That's what I've done at the moment, but only for > 9 > > columns, so anything > > over 9 fields will be truncated past and including > > field 10: > > wbau=# \d sys > > Table "public.sys" > > Column | Type | Modifiers > > +--+--- > > a0 | text | not null > > a1 | text | > > a2 | text | > > a3 | text | > > a4 | text | > > a5 | text | > > a6 | text | > > a7 | text | > > a8 | text | > > a9 | text | > > Indexes: > > "id" PRIMARY KEY, btree (a0) > > > > a0 = primary key - eg, ZPRECMPL or ZKCOST > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] > > On Behalf Of Aaron Bono > > Sent: Tuesday, 13 June 2006 2:12 PM > > To: pgsql-sql@postgresql.org > > Subject: Re: [SQL] COPY to table with array > columns > > (Longish) > > > > I think two tables should suffice: ZKCOST and > > ZPRECMPL. > > > > So you would have > > > > ZKCOST > > zkcost_id, > > zkcost_value > > > > and > > > > ZPRECMPL > > zkcost_id, > > zprecmpl_id, > > zprecmpl_value > > > > where zkcost_id is the primary key for ZKCOST and > > zkcost_id, > > zprecmpl_id together are the primary key for > > ZPRECMPL and zkcost_id is > > a foreign key from ZPRECMPL to ZKCOST. > > > > That will work won't it? > > > > -Aaron > > > > On 6/12/06, Phillip Smith > > <[EMAIL PROTECTED]> wrote: > > > > > So you're suggesting creating a child table for > > each SYS record? Ie, a > > table called "ZPRECMPL" etc? > > if the data is unrelated, then the data should be > separated (in a perfect world). > > can you convert into the following form: > > TABLE_ZKCOST > zkcost_id > zkcost_value > > TABLE_ZPRECMPL > zprecmpl_id > > TABLE_ZPRECMPL_DATA > zprecmpl_data_id > zprecmpl_id > zprecmpl_value just read Aaron's latest post - it is best to do as he suggested and link back to the sys table. TABLE_SYS sys_id TABLE_ZKCOST zkcost_id sys_id zkcost_value TABLE_ZPRECMPL zprecmpl_id sys_id TABLE_ZPRECMPL_DATA zprecmpl_data_id zprecmpl_id zprecmpl_value the difference, and i'm not sure it is significant, is that the above links the ZPRECMPL_DATA back to ZPRECMPL, then it links ZPRECMPL back to SYS. that's how my head wraps around this problem and i think it should work alright as long as you can massage the data into this format. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] SQL Technique Question
i frequently join certain tables together in various tables. view the following link for an example: http://www.rafb.net/paste/results/mBvzn950.html is it a good practice to leave this included in the queries, as is, or should i factor it out somehow? if i should factor it, how do i do so? tia... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL Technique Question
> > On Jun 16, 2006, at 5:59 , > <[EMAIL PROTECTED]> wrote: > > > i frequently join certain tables together in > various > > tables. > > > > view the following link for an example: > > > > http://www.rafb.net/paste/results/mBvzn950.html > > > > is it a good practice to leave this included in > the > > queries, as is, or should i factor it out somehow? > if > > i should factor it, how do i do so? > > I'm not quite sure what you're getting at in your > message (in > particular, what is the "this" in "leave this > included in the > queries"?), and it looks like I have a completely > different > interpretation of what you're asking, looking at the > responses you've > already received from Andrew and Rod. I think you're > asking about > encapsulation and how to efficiently use code, > rather than copying > and pasting the basic query and then modifying a > small portion of it. > Based on that interpretation, here's what I'd do. > (If I'm wrong, > well, then, oh well.) > > I'd create a view that contains the common code. > > CREATE VIEW t_inspect_join_view AS > > SELECT t_inspect_result.inspect_result_pass > , t_inspect_result.inspect_result_timestamp > , t_product.product_number > , t_inspect_result.inspect_result_id > FROM t_inspect_result, t_inspect, t_inspect_area, > t_serial_number, > t_link_contract_number_job_number, > t_job_number, t_product > WHERE t_inspect_result.inspect_id = > t_inspect.inspect_id > AND t_inspect.serial_number_id = > t_serial_number.serial_number_id > AND > t_serial_number.link_contract_number_job_number_id = > > t_link_contract_number_job_number.link_contract_number_job_number_id > AND > t_link_contract_number_job_number.job_number_id = > t_job_number.job_number_id > AND t_inspect.inspect_area_id = > t_inspect_area.inspect_area_id > AND t_product.product_id = > t_job_number.product_id; > > or in a style I find a bit clearer: > > CREATE VIEW t_inspect_join_view AS > > SELECT t_inspect_result.inspect_result_pass > , t_inspect_result.inspect_result_timestamp > , t_product.product_number > , t_inspect_result.inspect_result_id > FROM t_inspect_result > JOIN t_inspect USING (inspect_id) > JOIN t_serial_number USING (serial_number_id) > JOIN t_link_contract_number_job_number > USING (link_contract_number_job_number_id) > JOIN t_inspect_area USING (inspect_area_id) > JOIN t_job_number USING (job_number_id) > JOIN t_product USING (product_id); > > One of the reasons I like this style is that it > makes it easy to see > that all of the tables in the FROM clause have join > conditions (which > is usually what you want). For example, it looks > like you probably > want the > >AND t_inspect.inspect_area_id = > t_inspect_area.inspect_area_id > > part of the WHERE clause in your "repeating code" > section, so I've > added it to the view. With the JOIN conditions (how > tables are joined > together) now part of the FROM clause, the WHERE > clause can be used > to list just restrictions restrictions (limiting > what rows are > returned). I find this much clearer to write and > read, as I've got > clear separation between these two things. While > underneath it all > the server might consider everything part of the > WHERE clause, > sytactically I find this style helpful. > > I've also added some more columns to the SELECT > target list, as > you'll want to have them exposed for the extra WHERE > clause > restrictions. Once part of the view, only columns > listed in the > SELECT target list will be accessible outside of the > view. You may > have other restrictions that you want to apply in > different cases, so > you may want to add more columns to the target list. > > So your original query, using this view, would look > like: > > SELECT inspect_result_pass > FROM t_inspect_join_view > WHERE product_number = '7214118000' >AND inspect_result_timestamp > '2006-01-01' >AND inspect_result_timestamp < '2006-06-13' >AND inspect_result_id IN ... > > Hope this helps. > > Michael Glaesemann > grzm seespotcode net Michael, this is the answer to my question. i have to read up on the other answers to see if i can learn something to incorporate into my programming. i've just had a bear of a time trying to keep everything straight... okay, i want to know what inspects are associated with p/n 123, s/n 1... or what is the p/n associated with p/n 456? i had to troll through all my relations to get at results. i need to become mor familiar with views. if i use views, will i substantially impair performance? thanks for the answer... the end result sure sure looks clean. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner wi
Re: [SQL] SQL Technique Question
> On Thu, Jun 15, 2006 at 01:59:22PM -0700, > [EMAIL PROTECTED] wrote: > > > > is it a good practice to leave this included in > the > > queries, as is, or should i factor it out somehow? > if > > i should factor it, how do i do so? > > If what you're saying is that these additional > criteria are > redundant, then it's up to you: what do you want to > optimise for? If > you're protecting against future errors, then the > additional > criteria might help. If you're protecting against > having to write > your code to produce a more efficient query, you > should weigh the > cost and benefit (which benefit includes "easier to > debug queries"). > There is a probably non-zero cost to the extra > joins. Andrew and Rod, my apologies for not being more clear in my question. all the code is required to get from t_inspect_result data back to t_product information. however, many of the joins are used over and over and over - making for a complex query to view and try and to debug - not to mention forcing a long trail of chasing linked data to get from t_inspect_result_id back to the linked t_product data. Thanks for taking the time to address the question - and i will try and be more clear going forward. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Problem w/ Join... Using (...) Syntax
how does one apply the JOIN... USING (...) syntax when the primary key and foreign key have different names? JOIN t_employee USING (employee_id) works perfect when the primary and foreign key are both named employee_id. however, it one is named inspector_id and the other employee_id, it doesn't work - or at least i haven't been able to find the appropriate syntax through searching and trial and error. tia... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Data Entry and Query forms
> Hi > I am new in PostgreSQL, I have just installed and > created a database, I use MS-SQL 2000 and planning > to Migrate to Open Source. > > PostgreSQL is installed on Linux Redhat Server. > We have 50 Clients PC using WinXP. > > Before continuing our Plans, I need to know how > can I do with PostgreSQL in order to have Data Entry > and Query Forms on clients side (How can I design > Data Entry and Query Forms). > > If you can advice me the open source Software to > add on PostgreSQL to solve my problem will be > better. > > This will help the Management to accept PostgreSQL > and plan Migration from > MS-SQL 2000. > > Thanx > > Anthony Kinyage Anthony, welcome to the world of open source! you can continue to use windows programs (like msaccess) as your front end by connecting with odbc. however, i chose to use php (open source, too), html and css. it has worked out well. i use two php classes that have pretty dramatically increased my productivity. 1. Manuel Lemos' forms class (phpclasses.org) - this is a powerful class that enables lots of useful forms features. 2. adodb db abstraction layer php is relatively easy to pick up if someone has a desire to do so. i could even send you sample scripts to help guide you get started with adodb, the forms class and the code layout structure. i'm seriously looking into migrating my skills over to ruby on rails. you can review some screencasts here: http://www.rubyonrails.org/screencasts i'll be using postgresql as my db for any rails projects, too (many people use mysql). once one becomes proficient in ruby (language) and rails (framework), development becomes more efficient for many apps. many of the concepts (including object oriented programming) are more complex and require more up front planning - which may be more difficult for someone new to OO programming. so, i think php and ruby on rails would both likely work as front end programming languages / frameworks. of course, you'd need to work in html and css to display a layout. cross browser css code is difficult, but if you were interested, i will let you know about a resource that will help you a lot. good luck. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to serial primary key
> On 7/6/06, David Clarke <[EMAIL PROTECTED]> wrote: > > > > I posted a couple of weeks back a question > regarding the use of a 100 > > char column as a primary key and the responses > uniformily advised the > > use of a serial column. My concern is that the key > is effectively > > abstract and I want to use the column as a foreign > key in other > > tables. > > > I have a simple question... why do you want to use > the column as a foreign > key in other tables? If you use the serial column > then all you need is a > simple join to get the 100 char column out in your > query. If you need to > make things simpler, just create a view that does > the join for you. > > Either there is some requirement here that I am not > aware of or it sounds > like you may be trying to use a sledge hammer on a > nail. > > -Aaron i agree. all my primary keys are abstract - even though some don't have to be. iow, i'm comfortable using serials as my primary key even when i don't absolutely need to. in any case, the primary key is typically used as a unique identifer *and that's it*. the uniqueness makes it ideal for another table to use it to link related data. it sounds like you want a unique identifier *plus something else*. i'd argue that the "plus something else" belongs in a separate column. imho, your database life will be much easier and more consistent. good luck. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Foreign Key: what value?
> Hi,
> How to know the value which I must set in the
> foreign key field?. I have two
> tables:
>
>
> CREATE TABLE AA (
> Id SERIAL PRIMARY KEY,
> data char(9)
> );
>
> CREATE TABLE BB (
> BB_Id integer REFERENCES AA(Id) NOT NULL,
> field char(5)
> );
>
>
>
>
> I insert a register on table AA,
>
>INSERT INTO AA (data) VALUES ('123456789');
>
>
> and then, I want to insert a related register in
> table BB, but I do not
> know how get the proper value to the Foreign key
> BB_Id. Note that a lot of
> client are inserting at the same time on the AA
> table, so I can not just
> get the greater value of AA.Id
>
> Maybe using transactions ?. Any tip, URI, ... will
> be welcome.
>
>INSERT INTO BB (BB_Id, field) VALUES
> (??,'12345');
>
i alsways make my foreign key column data type int4.
i'm not sure if i read that somewhere. anyone, please
feel free to chime in if this isn't good practice.
read up on currval, nextval and that whole section.
you can begin by getting the nextval, assigning it to
a variable, insert it into your primary table and then
insert it into your related table as a foreign key.
from what i understand, either way should be
bulletproof. the way i described is more code, but
some minds might not mind paying that price b/c they
like the process better.
good luck.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Alternative to serial primary key
> Plus I feel I would be remiss in not > exploring an > alternative to the serial key. why? it is a tried and true method. > I can always > regenerate my primary key > from the data which is impossible with a serial key. why on earth would you need to "regenerate" the primary key? it is used to link related data in different tables, not as some sort of meaningful piece of data all by itself. even if you wanted to "regenerate" the primary key, using a serial is still easier. under the hash scheme, you have know the *exact* address... including abbreviation, periods, etc... if you use a serial, you could query the table using SQL's LIKE clause and get all the serial results that match the string you entered. if you enter the *exact* address, you'll get a single result - unless that address is entered twice. you can avoid that possibility by making address a unique column. when you link tables together, you can visually one big table with all the linked data... and that's what you can get when you join the tables. if you know one column's information (or part of it using LIKE), you can return any desired result in the same row (LIKE might return more than one result). > I believe the > risk of collision using md5 is effectively zero on > this data and I can > put a unique index over it. why risk it? what if you are wrong? > I'm kind of new to sql so apologies if this is a > naive approach. > Thanks to all for responses. no worries. i'm learning a ton as time goes on, myself. i do think you are over thinking this issue a bit, though. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] i have table
--- "Penchalaiah P." <[EMAIL PROTECTED]> wrote:
>
> Hi ...
>
> I have one table with 12 fields..
>
>
>
> CREATE TABLE addition_alteration_memo
>
> (
>
> addition_alteration_memo int8 NOT NULL DEFAULT
>
nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclas
> s),
>
> cda_no varchar(7) NOT NULL,
>
> week numeric,
>
> sheet_no numeric,
>
> serial_no numeric,
>
> date date,
>
> dr_no varchar,
>
> amount numeric,
>
> memo_no varchar,
>
> memo_date date,
>
> no_instalments numeric,
>
> instalment_rate numeric)
>
>
>
> now I want to add one more field in this table.. but
> that field has to
> come next to cda_no.. I mean as a 3rd field.. If I
> am adding that field
> it is coming last field ...
>
> may I know how it is possible to that table...
>
>
>
> Thanks & Regards
>
> Penchal reddy | Software Engineer
Penchal,
yes, you can do this, but it isn't necessary beyond
making you feel more comfortable when you look at your
column names in something like pgadmin3.
i don't have my code in front of me, but what you do
is export your table definitions into sql text. there
is a statement you can put at the beginning of the
table that deletes it if it already exists.
update the text file with the columns in order and
then import the sql table definitions.
your original table is deleted (if it exists) and the
new table is generated in the order you want it.
when i get some time on the computer where i've done
this, i will post an example.
oe1
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Organization of tables
Hi Salman,
will this achieve your needs?
peptide_table
primary_key
name (ie, Peptide1,
Peptide2)
mass
fragment_table
primary_key
name (ie, A, Q,
K)
link_table
primary_key
peptide_id
fragment_id
it gives you easy access to peptide mass and it allows you to link multiple
fragments to individual peptides.
if the peptide mass ultimately comes form the fragments, you could enter the
mass value there and then have the db do a calculation to sum up the masses for
a given peptide.
sorry if i've misunderstood you problem.
best of luck,
oe1
- Original Message
From: Salman Tahir <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thursday, June 14, 2007 4:21:06 AM
Subject: [SQL] Organization of tables
Hi,
I have a question regarding the organization of a table I want to
create in my database: I have the following setup:
Table Fragments (simplified example):
Primary key = {mass}
Approach (1)
fragment | mass (of fragment) | peptide
---++--
A | x | Peptide1, Peptide2
Q | y | Peptide1
K | z | Peptide 2, Peptide3
The idea here is that a peptide can be composed of many fragments e.g.
Peptide 2 is made up of fragments A and K; Peptide1 is made up of A
and Q and so on.
My idea is to create an index on the mass column and be able to
retrieve all Peptides that contain a certain fragment mass e.g
SELECT peptide
FROM Fragments
WHERE mass = x;
Should give me: Peptide1, Peptide2
The alternative way I have thought of to organize this table is to
have something as follows:
Approach (2)
Primary Key = {fragment, mass, peptide}
fragment | mass (of fragment) | peptide
---++--
A | x | Peptide1
A | x | Peptide2
Q | y | Peptide1
K | z | Peptide 2
K | z | Peptide 3
If I consider 2500 unique fragments then, using approach (1), table
Fragments will hold 2,500 tuples. If I consider the same number of
fragments then table Fragments using approach 2 holds 15,000 tuples.
I have considered using approach (1) whereby I would have less tuples
to search but if I wanted to access the peptides they belong to I
would retrieve the list of corresponding peptides e.g the string
"Peptide1, Peptide2" and process it in my program. However this seems
like a hack around the way a database table should be organised. The
problem increases further when I have to scale up and consider more
unique fragments (>2500).
Any help on how best to structure such data would be mostly appreciated.
- Salman Tahir
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.
http://tv.yahoo.com/
---(end of broadcast)---
TIP 6: explain analyze is your friend
