Re: [SQL] Advanced Query
On Jun 6, 2006, at 12:32 PM, Andrew Sullivan wrote: On Tue, Jun 06, 2006 at 09:20:13AM -0700, codeWarrior wrote: I would hope that your choice to use postgreSQL is because it is superior technology that scales well financially... not because you get a warm fuzzy from all your friends on the mailing lists... I would hope that the tone of the mailing lists might be maintained in much the high one demonstrated by such polite, helpful, and smarter-than-me people as those on the PostgreSQL core team. I note that the above troll does not qualify. I suggest people avoid feeding it. update thread set response = response + 1; A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Daryl self email: ( daryl at: eddl dot: us ) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Join issue
My current solution to the problem is PHP and it - simply put - sucks I loop through all the house records for a user using the following query SELECT * FROM ce_house 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_house.user_id='1' and then submit an additional query for each result to find the provider rate by comparing ce_contract_created and ce_provider_rate created. SELECT provider_rate_amount FROM ce_provider_rate WHERE provider_id='".$row["provider_id"]."' AND provider_rate_created<='".$row["contract_created"]."' ORDER BY provider_rate_created DESC LIMIT 1 OFFSET 0 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 Would I violate design principles if I create a new field called "ce_contract_rate" under "ce_contract" and populate it as soon as a new contract is created instead of looking it up from the ce_provide_rate table everytime i need it? Regards, Burak On 6/6/06, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Jun 7, 2006, at 8:53 , Kenneth B Hill wrote:> Make a "view" with a> query, then perform a query using the view, etc. , then drop all views> in the SQL script. This may make the entire operation perform faster. I don't know how using a view would improve performance. However, itmay make the overall query more tractable by encapsulating portionsof it using views.Michael Glaesemanngrzm seespotcode net
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 ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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
Re: [SQL] How To Exclude True Values
> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Query to return modified results at runtime?
I have a query: SELECT * FROM testtable; Where the results are: IDColor --- --- 1 Blue 2 Red 3 Green 4 Orange How would I rewrite the query to return results where the colors are replaced by letters to give the following results? IDColor --- --- 1 A 2 D 3 B 4 C ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query to return modified results at runtime?
> IDColor > --- --- > 1 Blue > 2 Red > 3 Green > 4 Orange > > How would I rewrite the query to return results where the colors are > replaced by letters to give the following results? > > IDColor > --- --- > 1 A > 2 D > 3 B > 4 C http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html this is probably the easiest to implement but hard to mangage over time. Another solution would be to create color_code table that is referenced by your test table. Then when you can create a query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color; There are additional solutions to this also. But these two are probably the easiest. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
