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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Marco Sent: Friday, March 25, 2011 10:10 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
