Search Postgresql Archives

Re: last entry per person

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 03/02/2012 08:30, Chris Travers wrote:


On Fri, Feb 3, 2012 at 12:26 AM, garry <garry@xxxxxxxxxxxxxxx> wrote:
I have a table which holds a user name and their results in exams. There can be multiple entries per user. I am trying to return the last entry for each user. I can get the last entry in the table using the order by/limit method but how would this be applied per user. My table definition is like the following:

gradeid serial primary key,
user text,
grade char(1),
entered timestamp,
Any help would be appreciated.
Regards

What about a combination of a common table _expression_ and a windowing function?  You ought to be able to order by your criteria and then pull where the rowcount in the window is 1.

Best Wishes,
Chris Travers
 

Garry



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


There is no simple solution like there is for the last row in the table.
Window functions are one option, sub queries are another:

SELECT *
  FROM results,
           ( SELECT student_id,
                    max(result_date) result_date
               FROM results
           GROUP BY student_id
           ) as latest
 WHERE results.student_id = latest.student_id
   AND results.result_date = latest.result_date

Note that for this to work correctly, result_date will need to be unique.

Regards

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux