[SQL] sql group by statement
Hello, I have a problem, which I'm not able to solve with a simple query : I need a resultset with distinct id's, but the max val2 of each id. I tried to group by id, but though I need the pk in my resultset I have to group it too, which "destroys" the group of val2. Can this be done without a huge query ? Table : pk id val1 val2 112 3 212 4 321 1 410 5 521 8 Needed Result : pk id val1 val2 410 5 521 8 Thx berger ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql group by statement
see the yesterday's thread about DISTINCT ON (non-standard Postgres feature) > I have a problem, which I'm not able to solve with a simple query : > > I need a resultset with distinct id's, but the max val2 of each id. > I tried to group by id, but though I need the pk in my resultset > I have to group it too, which "destroys" the group of val2. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql group by statement
but how do I know that "distinct on" doesn't cut off the row with max(val2) of that id that I need ? > see the yesterday's thread about DISTINCT ON (non-standard Postgres feature) > > > I have a problem, which I'm not able to solve with a simple query : > > > > I need a resultset with distinct id's, but the max val2 of each id. > > I tried to group by id, but though I need the pk in my resultset > > I have to group it too, which "destroys" the group of val2. > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Assignments in PL/pgSQL
Hi all, although this is not really SQL, but PL/pgSQL, I hope this is the right place to ask. I have written a complex triggers. It works very well. Just now I have realized that I have used the = operater for variable assignments, instead of the := operater. To my suprise, there was no error or warning, and the store procedure works very well! e.g. NEW.someval = rec.someother; works as well as NEW.someval := rec.someother; Can you confirm that both are valid? Can someone explain this? And is it safe to use the former syntax? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Table alias in DELETE statements
Hi, I've already checked the mailing archive but found nothing about problems with the DELETE statement and table alias but it's not mentioned in the docs. So the question for me is whether it is possible to use a table alias in a DELETE statement or not, e.g. DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality and I really need the alias because the SQL is generated automaically by a tool)? Thanks, Hanno Wiegard __ Die clevere Geldreserve: der DiBa-Privatkredit. Funktioniert wie ein Dispo, ist aber viel gunstiger! Alle Infos: http://diba.web.de/?mc=021104 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sql group by statement
On Fri, 13 Sep 2002 12:29:21 +0200, "Albrecht Berger" <[EMAIL PROTECTED]> wrote: >Table : >pk id val1 val2 > 112 3 > 212 4 > 321 1 > 410 5 > 521 8 > > >Needed Result : >pk id val1 val2 > 410 5 > 521 8 Albrecht, "DISTINCT ON eliminates rows that match on all the specified expressions, keeping only the first row of each set of duplicates." So the trick is to sort appropriately: SELECT DISTINCT on (id) pk, id, val1, val2 FROM yourtable ORDER BY id asc, val2 desc, pk desc; Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table alias in DELETE statements
On Fri, 13 Sep 2002 14:10:25 +0200, Hanno Wiegard <[EMAIL PROTECTED]> wrote: >So the question for me is whether it is possible >to use a table alias in a DELETE statement or not, e.g. >DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality Hanno, looks like you are out of luck here. PG 7.3: DELETE FROM [ ONLY ] table [ WHERE condition ] SQL92: DELETE FROM [ WHERE ] SQL99: DELETE FROM [ WHERE ] ::= [ ONLY ] which BTW makes "DELETE FROM mytable" invalid. This would have to be "DELETE FROM (mytable)". Is there something wrong with my copy of the standard? There has been a discussion on -hackers about extensions to the DELETE statement (DELETE [FROM] a FROM b, c WHERE ...). If this gets implemented in a future release, there's a certain chance for a table alias. >and I really need the alias because the SQL is generated automaically >by a tool)? ... and this tool works with what database? Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
