Re: [SQL] Update Problem
On Tue, Jun 06, 2006 at 01:04:26PM -, Christian Paul Cosinas wrote: > > > Sometimes only column1 and column2 is updated and column3 is not updated. Column 3 was already set to 08:00 AM? A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Advanced Query
Personally: I think your posts are getting annoying. This isn't SQLCentral. Learn to write your own damn queries or even better - buy a book on SQL... <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Advanced Query
> Personally: I think your posts are getting annoying. This isn't SQLCentral. > Learn to write your own damn queries or even better - buy a book on SQL... Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed following threads like these. Even when the questions (to some) seems overly simplistic, the courteous respondents often share insightful solutions or nuances that are not found in an "off the self" SQL book. However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in knowing what kind of threads are acceptable and on-topic for this list. Also, if there are other mailing lists (pg or other) that are better suited for threads like this, I would appreciate learning of them. Regards, Richard Broersma ---(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] Advanced Query
Richard Broersma Jr wrote: Personally: I think your posts are getting annoying. This isn't SQLCentral. Learn to write your own damn queries or even better - buy a book on SQL... Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed following threads like these. Even when the questions (to some) seems overly simplistic, the courteous respondents often share insightful solutions or nuances that are not found in an "off the self" SQL book. However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in knowing what kind of threads are acceptable and on-topic for this list. Also, if there are other mailing lists (pg or other) that are better suited for threads like this, I would appreciate learning of them. Regards, Richard Broersma ---(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 I would like to say that I have found the level of patience and help offered by the members of the mailing lists to be a key factor in my choice to use Postgres. And I feel that the response above would have been the sort of thing that would have turned me off., even if it was not in response to one of my own posts. Sometimes I may not understand the questions being asked or the answers being given but it sure is great to be able to search and find them later when you are the one hitting that same wall. If someone on a list like this rubs you the wrong way, personally I would ignore them, hence I expect to get zero responses to this! ;) Just my 2c this is a great resource and I would hope all feel as welcome here as I have, Oisin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Advanced Query
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... "Oisin Glynn" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Richard Broersma Jr wrote: >>> Personally: I think your posts are getting annoying. This isn't >>> SQLCentral. Learn to write your own damn queries or even better - buy a >>> book on SQL... >>> >> >> Personally: (being a newbie with an interest in developing a strong rdms >> skillset) I've enjoyed >> following threads like these. Even when the questions (to some) seems >> overly simplistic, the >> courteous respondents often share insightful solutions or nuances that >> are not found in an "off >> the self" SQL book. >> >> However, if questions like these are *really* off-topic for the pgsql-sql >> I would be interested in >> knowing what kind of threads are acceptable and on-topic for this list. >> >> Also, if there are other mailing lists (pg or other) that are better >> suited for threads like this, >> I would appreciate learning of them. >> >> Regards, >> >> Richard Broersma >> >> ---(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 >> > I would like to say that I have found the level of patience and help > offered by the members of the mailing lists to be a key factor in my > choice to use Postgres. And I feel that the response above would have been > the sort of thing that would have turned me off., even if it was not in > response to one of my own posts. Sometimes I may not understand the > questions being asked or the answers being given but it sure is great to > be able to search and find them later when you are the one hitting that > same wall. If someone on a list like this rubs you the wrong way, > personally I would ignore them, hence I expect to get zero responses to > this! ;) > > Just my 2c this is a great resource and I would hope all feel as welcome > here as I have, > > Oisin > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Advanced Query
On Tue, 2006-06-06 at 10:30, Richard Broersma Jr wrote: > > Personally: I think your posts are getting annoying. This isn't SQLCentral. > > Learn to write your own damn queries or even better - buy a book on SQL... > > Personally: (being a newbie with an interest in developing a strong rdms > skillset) I've enjoyed > following threads like these. Even when the questions (to some) seems overly > simplistic, the > courteous respondents often share insightful solutions or nuances that are > not found in an "off > the self" SQL book. > > However, if questions like these are *really* off-topic for the pgsql-sql I > would be interested in > knowing what kind of threads are acceptable and on-topic for this list. > > Also, if there are other mailing lists (pg or other) that are better suited > for threads like this, > I would appreciate learning of them. Personally, I too enjoy these threads. And when I find someone annoying (not you, by the way) I just ignore them. It's a modern world. there are these things called filters. I use them to ignore people that annoy me, rather than demanding they stop posting. Like my momma says, if you can't say anything nice, don't say anything at all. Keep posting. If I don't wanna read / answer your posts, I won't. If I've got 5 free minutes, I will. I suggest codeWarrier do the same. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Advanced Query
On Tue, Jun 06, 2006 at 08:30:54AM -0700, Richard Broersma Jr wrote: > > However, if questions like these are *really* off-topic for the > pgsql-sql I would be interested in knowing what kind of threads are > acceptable and on-topic for this list. They're not off-topic. The point of the list is indeed to answer "how do I do this with SQL"? If one doesn't want to see such questions, one might unsubscribe. -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Advanced Query
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. 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
Re: [SQL] Advanced Query
Don't forget that support is a very important part of making a decision about whether to or not to use a technology. Having people who are happy to read and respond to any question is part of great support for the product. And I am glad to see that most people on this list agree with me on the importance of even the basic questions like this.What was the original question again? Heh.-Aaron On 6/6/06, codeWarrior <[EMAIL PROTECTED]> wrote: I would hope that your choice to use postgreSQL is because it is superiortechnology that scales well financially... not because you get a warm fuzzyfrom all your friends on the mailing lists...
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? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Join issue
Hi,I have one to many (from left to right) relationships among the below tables in my databaseuser -> 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
Re: [SQL] Join issue
On Tue, Jun 06, 2006 at 11:10:46AM -0700, Burak Seydioglu wrote: > 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. I don't have a proposal to rewrite, but I suspec this is going to be easier with a query to get the latest provider rate in the FROM clause. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(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
> > 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
[SQL] How to get list of days between two dates?
Hello,
I'm trying to write a query and cannot figure out how to do it (or
whether it can be done in SQL alone). Given a table containing events
with their starting and ending days (may be single- or multi-day
events), I need a list of the events occurring each day:
CREATE TABLE test_events (
event_id serial,
event_name text,
start_time date,
end_time date,
CONSTRAINT event_pkey PRIMARY KEY (event_id)
);
INSERT INTO test_events (event_name, start_time, end_time) VALUES
('First Event', '05/01/2006', '05/04/2006');
INSERT INTO test_events (event_name, start_time, end_time) VALUES
('Second Event', '05/02/2006', '05/02/2006');
INSERT INTO test_events (event_name, start_time, end_time) VALUES
('Third Event', '05/04/2006', '05/05/2006');
INSERT INTO test_events (event_name, start_time, end_time) VALUES
('Fourth Event', '05/07/2006', '05/07/2006');
The query results should look like:
5/1/2006First Event
5/2/2006First Event
5/2/2006Second Event
5/3/2006First Event
5/4/2006First Event
5/4/2006Third Event
5/5/2006Third Event
5/7/2006Fourth Event
I've been experimenting with set-returning functions, but I haven't
stumbled on the answer. Suggestions?
Thanks
--christine
Christine Desmuke
Database Administrator
Kansas State Historical Society
Topeka, KS
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to get list of days between two dates?
Though there may be a more eligant way to do it, when we did things like this in the past we created a function (or stored procedure) that got the min and max dates and then created a result set that iterated through the dates to create a virtual table of days. Then you can inner join that list of days with your physical table.
I am interested in other approaches though.-AaronOn 6/6/06, Christine Desmuke <[EMAIL PROTECTED]
> wrote:Hello,I'm trying to write a query and cannot figure out how to do it (or
whether it can be done in SQL alone). Given a table containing eventswith their starting and ending days (may be single- or multi-dayevents), I need a list of the events occurring each day:CREATE TABLE test_events (
event_id serial, event_name text, start_time date, end_time date, CONSTRAINT event_pkey PRIMARY KEY (event_id));INSERT INTO test_events (event_name, start_time, end_time) VALUES
('First Event', '05/01/2006', '05/04/2006');INSERT INTO test_events (event_name, start_time, end_time) VALUES('Second Event', '05/02/2006', '05/02/2006');INSERT INTO test_events (event_name, start_time, end_time) VALUES
('Third Event', '05/04/2006', '05/05/2006');INSERT INTO test_events (event_name, start_time, end_time) VALUES('Fourth Event', '05/07/2006', '05/07/2006');The query results should look like:5/1/2006First Event
5/2/2006First Event5/2/2006Second Event5/3/2006First Event5/4/2006First Event5/4/2006Third Event5/5/2006Third Event5/7/2006Fourth Event
I've been experimenting with set-returning functions, but I haven'tstumbled on the answer. Suggestions?
Re: [SQL] How to get list of days between two dates?
On Tue, 2006-06-06 at 16:17, Aaron Bono wrote: > Though there may be a more eligant way to do it, when we did things > like this in the past we created a function (or stored procedure) that > got the min and max dates and then created a result set that iterated > through the dates to create a virtual table of days. Then you can > inner join that list of days with your physical table. If you're on a version of pgsql with generate_series, you can use that to easily create a pivoting data set on the fly to do this. If you're on an older version, it's easy enough to make a generic pivot table and keep it around to do these things. Since we're still on 7.4 where I work, we use the pre-populated pivot table. It's primitive, but it works. ---(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_iir_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_iir_id testmstamp 4 8 No 6/5/2006 8:00:00 AM Regards, Richard Broersma Jr. ---(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] 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] How To Exclude True Values
> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Join issue
That looks like a very complex query. I would like to suggest that you try doing some nesting queries via a SQL script. 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. -Ken On Tue, 2006-06-06 at 15:33 -0700, [EMAIL PROTECTED] wrote: > > 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Join issue
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, it may make the overall query more tractable by encapsulating portions of it using views. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to get list of days between two dates?
This is going to be ugly, and I can't even say for sure it's right (and if by
chance it is right, I imagine it still might be more efficient broken up in a
function), but intrigued by learning about generate_series() from Scott
Marlows response I fiddled until I got the results specified like this...
SELECT dt, event_name
FROM (
SELECT (mn.d + s.d) AS dt
FROM (
SELECT min(start_time) FROM test_events) AS mn(d),
generate_series(0, (
SELECT (extract('epoch' from age(max(end_time),
min(start_time)))/86400)::integer
FROM test_events))
AS s(d))
AS x
JOIN test_events AS y ON (dt BETWEEN start_time AND end_time)
ORDER BY dt, event_name;
dt | event_name
+--
2006-05-01 | First Event
2006-05-02 | First Event
2006-05-02 | Second Event
2006-05-03 | First Event
2006-05-04 | First Event
2006-05-04 | Third Event
2006-05-05 | Third Event
2006-05-07 | Fourth Event
(8 rows)
--
Tim Middleton | Vex.Net| "Who is Ungit?" said he, still holding
[EMAIL PROTECTED] | VexTech.ca | my hands. --C.S.Lewis (TWHF)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
