Re: select items, and max id

Am Fr, 2003-08-29 um 04.11 schrieb Luis H.:
> I have a table where each row has an id unique identifier (separate
> from the one postgreql does automatically).

I would declare this as primary key too, not only unique.
Due to the fact that you didn't mention the words "primary key" I assume
you only have a unique field.
I think it could be useful for you to have something like

CREATE TABLE my_table (
 id_my_table SERIAL PRIMARY KEY,
 ...
 ...
);

SERIAL is in fact of type int but will be auto incremented with each
insert (if omitted).
PRIMARY KEY makes it UNIQUE and NOT NULL automatically and will use it
as default for FOREIGN KEYs.

That wasn't your question, but I think it can't hurt :)

> I want to be able to do a certain SELECT query returning various rows,
> and also be able to check what the maximum id was among the rows in
> the result. Is it possible to do this all in the same query? Right now
> I do it in a very dumb way -- I execute the query to get all the data,
> and then I re-execute it as a subquery and pull out the max(id).

The related keywords are:
aggregate functions
 and
GROUP BY

max is an aggreate function as it does things on all columns but returns
only 1 result.
Every other field you select in the query has to be also in an aggreate
function OR has to be grouped.

e.g.:

given the following table tempo:

id | num | txt
---------------
1  | 16  | a
2  | 23  | a
3  | 25  | b
4  | 25  | b

SELECT max(num) FROM tempo;

gives exactly 1 result:
25

SELECT max(num), txt FROM tempo;
is not possible.

possible would be e.g.
SELECT max(num), avg(num) FROM tempo;
=> 25,22.25

OR if you want the maximum of a GROUP:

SELECT max(num), txt FROM tempo GROUP BY txt;
23,a
25,b

So, as Bruno already stated :), it is not possible to have non-grouped
queries with aggregates and non-aggregates mixed without duplicating the
query (e.g. by a subselect).

BTW: If you want aggregate functions in the WHERE clause you need a
HAVING clause.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de