Re: [SQL] How can I get the last element out of GROUP BY sets?
> > I'm trying to produce summary data from a table (using PGSQL 7.4.1): > > CREATE TABLE readings( "when" timestamp, value integer ); > > The summary will be based on various time periods. I've been using date_trunc( > 'hour', "when" ) and GROUP BY for the min/max/average readings with no problems. > But, one piece of data I need is the last value for each GROUP BY period. Alas, > I cannot figure out how to do this. > > If I wanted to loop from a script, I could, for instance, execute the following > for each GROUP BY period (filling in ? appropriately): > > SELECT date_trunc( 'hour', "when" ), value > FROM readings > WHERE date_trunc( 'hour', "when" )::timestamp = ? > ORDER BY "when" DESC > LIMIT 1 > > But, I figure there's probably some what to do this in SQL. > > Any help? > > Thanks, > Rob > Looks like "SELECT DISTINCT ON" is your friend. RTFM. HTH Regards, Christoph ---(end of broadcast)--- TIP 3: 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] Initially Deffered - FK
On Fri, 16 Jan 2004 [EMAIL PROTECTED] wrote: > Thanks for your reply. > > But, you will agree that result should be same JUST BEFORE and JUST AFTER > commit ( assuming no one is working on the database and i am the only user > connected.) If you use the definition we're using now, then no, the above is not true. The row would still exist just before commit because the on delete action has not happened. The row would not exist just after commit because the on delete action now has. Whether this definition is correct per spec is hard to say, but there've been arguments on the subject in the past that have generally ended without a firm understanding of the specs intention. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] name of a column returned from a table function
(B (B (BDB Version 7,4. (B (BI had a look through the docs, but the (Binformation on table functions (those returning SETOF something) seems to have (Bgone missing from the 7.4 docs. I found something under 7.3, but it didn't (Banswer my question, which is... (B (BI've written a little table function that (Bsimply returns all the dates in a month. No problems so far. Now I want to outer (Bjoin this list to another table, but I dont know what to call the column (Breturned by the function. All the examples I saw just had "select * from (B". (B (BI want do something like "select (B,... from join on (B( = ; (B (BSearching the lists, I found something to (Bthe effect that this might have problems. Is that still the case? (B (BThanks (BIain
Re: [SQL] Initially Deffered - FK
Hi Stephan, Thanks for your reply. Will have to work on its workaround... Denis - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, January 19, 2004 4:03 PM Subject: Re: [SQL] Initially Deffered - FK > > On Fri, 16 Jan 2004 [EMAIL PROTECTED] wrote: > > > Thanks for your reply. > > > > But, you will agree that result should be same JUST BEFORE and JUST AFTER > > commit ( assuming no one is working on the database and i am the only user > > connected.) > > If you use the definition we're using now, then no, the above is not true. > The row would still exist just before commit because the on delete action > has not happened. The row would not exist just after commit because the > on delete action now has. Whether this definition is correct per spec is > hard to say, but there've been arguments on the subject in the past that > have generally ended without a firm understanding of the specs intention. > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] name of a column returned from a table function
"Iain" <[EMAIL PROTECTED]> writes: > I've written a little table function that simply returns all the dates in a > month. No problems so far. Now I want to outer join this list to another > table, but I dont know what to call the column returned by the > function. Same as the name of the function, unless you supply a column alias to change it to something else. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How can I get the last element out of GROUP BY sets?
When grilled further on (Mon, 19 Jan 2004 00:44:30 -0500), Tom Lane <[EMAIL PROTECTED]> confessed: > Robert Creager <[EMAIL PROTECTED]> writes: > > ... one piece of data I need is the last value for each GROUP BY > > period. Alas, I cannot figure out how to do this. > > SELECT DISTINCT ON (rather than GROUP BY) could get this done for you. > I had my whine all ready as to how I still couldn't figure it out, when I figured it out: ... SELECT p.period, etday FROM (SELECT DISTINCT ON ( period ) date_trunc( 'hour', "when" ) AS period, etday FROM readings ORDER BY period, "when" DESC) AS p ... Thanks for the tip. Cheers, Rob -- 08:10:55 up 21 days, 21:58, 4 users, load average: 2.15, 2.06, 2.02 pgp0.pgp Description: PGP signature
Re: [SQL] Trigger to identify which column(s) updated
Dear Jack , for each column in new: if (new.column(1) != old.column(1)) ... ...and then some snippet of code to determine the primary key column of new (or old) Check the following link http://gborg.postgresql.org/project/tablelog/projdisplay.php Hope this helps -- Regards, Vishal Kashyap ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* I Know you believe my words so logon to Jabber.org and add [EMAIL PROTECTED] to your roster. ~*~*~*~*~*~*~*~* I am usually called as Vishal Kashyap but my Girl friend calls me as Vishal CASH UP. This is because others know me because of my generosity and my Girlfriend knows me because of my CASH. ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Database diagram
I have a postgres database with many tables. How do I create database diagram? Are there any free tools available to create database diagram from pgsql database? Regards, Ganesan ---(end of broadcast)--- TIP 8: explain analyze is your friend
