[SQL] ids from grouped rows

[Lindsay]

Lets say i do this:

SELECT name, MAX(age)
FROM Person
GROUP BY name

This will group people who have the same name together, and return the
highest age for each group. I want to be able to find the id for the
person with the highest age in each group  -

Ideally, it would be something like this 

SELECT name, MAX(age), id_for_row_with_max_age
FROM Person
GROUP BY name

Anyone know how to do this?

Lindsay

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Inheritence and Integrity

[Neal Lindsay]

I am creating a database that will keep track of several different types 
of 'events'. I am toying with the idea of making a base 'class' table 
for the tables because a lot of the information will be the same (also 
there will probably be times I just need to get the basic information 
about events regardless of their type). My question is: will triggers 
and rules on the parent table fire when I insert data in the child 
tables? Are there any other potential pitfalls?

Thank you,
-Neal Lindsay

P.S. Here is a simplified example of my schema:

CREATE TABLE parenttable (
	recordid SERIAL PRIMARY KEY,
	recordname text
);

CREATE TABLE childtablea (
	afield int4
) INHERITS parenttable;

CREATE TABLE childtableb (
	bfield text
) INHERITS parenttable;

CREATE TABLE extrainfo (
	extrainfoid SERIAL PRIMARY KEY,
	record_fkey int4 NOT NULL REFERENCES parenttable(recordid),
	extrainfotext text
);


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Inheritence and Integrity

[Neal Lindsay]

Stephan Szabo wrote:

On Wed, 29 Jan 2003, Neal Lindsay wrote:

I am creating a database that will keep track of several different types
of 'events'. I am toying with the idea of making a base 'class' table
for the tables because a lot of the information will be the same (also
there will probably be times I just need to get the basic information
about events regardless of their type). My question is: will triggers
and rules on the parent table fire when I insert data in the child
tables? Are there any other potential pitfalls?



Currently that won't do what you want because triggers are not inherited
and the constraint is set up so the references constraint ends up being
only on the rows in parenttable.  In addition, the primary key constraint
won't do what you probably want either, although since it's a serial, you
won't be likely to notice.



So what you're saying is that I could insert a duplicate primary key 
into the parent table by inserting an explicit value in that field in my 
child table? And if I leave that column out of my insert statement the 
"default nextval()" will still make it a unique value?

If that is so, is there a way to make constraint that will keep primary 
keys unique across all the child tables of my parent table?

-Neal Lindsay


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] automatic time/user stamp - rule or trigger?

[Neal Lindsay]

Jan Wieck wrote:

A rule will not work here because rules cannot cause the same action on
the same table they are called for.



A-ha! I guess that's what I wasn't understanding. Triggers it is then.

Thanks,
-Neal


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] automatic time/user stamp - rule or trigger?

[Neal Lindsay]

I have a table that I want to keep track of the user who last modified 
it and the timestamp of the modification. Should I use a trigger or a rule?

CREATE TABLE stampedtable (
	stampedtableid SERIAL PRIMARY KEY,
	updatestamp timestamp NOT NULL DEFAULT now(),
	updateuser name NOT NULL DEFAULT current_user,
	sometext text
);

I suspect that I want a rule, but all the examples in the documentation 
seem to update a separate table and not override (or add) the 
insert/update to the timestamp and name columns.

Thanks,
-Neal


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster