If you group by a unique value you in effect perform no grouping at all...
What you need to do, in a subquery, is find the max(date) over the data you
want to group by. Then, in the outer query select the record(s) that match
that date. It is in the outer query where you can then add in any
additional fields that are associated with the filtered records.
SELECT *
FROM table t
INNER JOIN (SELECT id, max(date) AS limitdate FROM table GROUP BY id)
limiter
WHERE t.id = limiter.id AND t.date = limiter.limitdate
It becomes somewhat simpler if you have a single value PRIMARY KEY on table:
SELECT * FROM table WHERE table.pk = (SELECT pk FROM table tsub WHERE
tsub.id = table.id ORDER BY tsub.date DESC LIMIT 1);
There are also other variations but the concept holds. You MUST be using a
form of sub-query (or JOIN) to make it work.
Also keep in mind that if you want to ensure only a single record per ID
that an ID cannot be associated with the same date more than once. The
second query mitigates this by using a PRIMARY KEY along with a "LIMIT 1"
clause. The first query, however, can return multiple records with the same
ID if they share the same date.
David J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marco
Sent: Friday, March 25, 2011 10:10 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help for constructing query
Just want to add:
> 1) Display the rows with the highest date per id. That gives as many rows
as
> ids exist.
>
> select id, max(date) from mytable group by id;
>
> gives just the id and the date, not the other values. I think of doing
> this in two steps:
select id, max(date),min,value,max from mytable group by id,min,value,max;
Gives too many rows. I don't know why. If I can manage to fix the query
above just to output one row per id I can solve it.
Marco
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
