Maxima per row
От | Moray McConnachie |
---|---|
Тема | Maxima per row |
Дата | |
Msg-id | 00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk обсуждение исходный текст |
Ответы |
Re: Maxima per row
|
Список | pgsql-sql |
I have a table, one of whose fields indicates the date from which information in the row becomes valid, and another indicates the type to which the row refers. How do I design a query such that I get one row for each type, that row being the most recent info about that type? I.e. what I want is CREATE TABLE info (id serial primary key,typeid int4,validfrom date,description text); INSERT INTO info (typeid,validfrom,description) VALUES (1,'01/01/1999','a'); INSERT INTO info (typeid,validfrom,description) VALUES (1,'01/01/2000','b'); INSERT INTO info (typeid,validfrom,description) VALUES (2,'01/01/1998','c'); INSERT INTO info (typeid,validfrom,description) VALUES (2,'01/01/1999','d'); INSERT INTO info (typeid,validfrom,description) VALUES (2,'01/01/1997','e'); SELECT typeid,validfrom FROM prices GROUP BY typeid HAVING validfrom=MAX(validfrom); but of course the last is illegal. The result I'm looking for is 1,'01/01/2000','b', 2,'01/01/1999','d' Thanks, Moray ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk
В списке pgsql-sql по дате отправления: