[SQL] sql group by statement

[Albrecht Berger]

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

[Albrecht Berger]

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] replace null with 0 in subselect ?

[Albrecht Berger]

Hello,
I have a statement like this :

INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2);

This works fine if the subselect returns a value, but if it returns
null there is a problem. In this case a 0 has to be inserted.
Is there any pg function which solves this problem ?
I know that oracle has a function but I didn't find
something similar in pg.

Thanks
berger



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Chaning locale sorting order for statements

[Albrecht Berger]

Hello,
is it possible to change the locale sorting order in statements ? 
I need this for a multilingual web application, where I'm not
able to set the locale in the session or elsewhere.

In Oracle I would use something like that :
SELECT * FROM table1
ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');

Does postgres has something similar ?

Thx
berger

---(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

[SQL] Inheritance in SQL99 ?

[Albrecht Berger]

Hello,
is the inheritance of tables specified in the SQL99 standard, 
or is this a postgresql "add-on" ?

Does anybody know when the primary key bug, which is
documented in the docs,  of this feature will be fixed ?

Thx
berger

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] trigger : emulate "instead of" with before ?

[Albrecht Berger]

Hello,
I need a trigger which is updating a delete flag of a row instead of
deleting it physically.

How do I implement a trigger which doesn't execute a delete without raising
an exception
in plsql ?

CREATE TRIGGER trigger1 BEFORE DELETE ON table1
FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag ();

If I use
RAISE EXCEPTION "..."
the delete statement isn't executed, but I think the transaction is rolled
back too, am I right ?

So what is the best way to implement something (Oracle like) like that :
CREATE TRIGGER trigger1 INSTEAD OF DELETE ON table1
FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag ();

thx


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html