On 03/02/2012 08:30, Chris Travers wrote:
On Fri, Feb 3, 2012 at 12:26 AM, garry <[email protected]
<mailto:[email protected]>> 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 ([email protected]
<mailto:[email protected]>)
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
