last entry per person

On Fri, Feb 3, 2012 at 12:26 AM, garry <garry@scholarpack.com> 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 <garry@scholarpack.com> 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
Garry

What about "select user, max(entered) from table group by user" ?

Btw.: user is a reserved word, don't use it as column-name.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

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

On Fri, Feb 3, 2012 at 12:26 AM, garry <garry@scholarpack.com
<mailto:garry@scholarpack.com>> 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@postgresql.org
<mailto:pgsql-general@postgresql.org>)
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

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

On Fri, Feb 3, 2012 at 12:26 AM, garry <garry@scholarpack.com
<mailto:garry@scholarpack.com>> 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.

Unfortunately I am using a version of postgres without the windowing
functions, but that would have done the job.
Regards
Garry

On 2012-02-03, garry <garry@scholarpack.com> wrote:

This is a multi-part message in MIME format.
--------------060709070909070009090305
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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,

select distinct on (user) * from EXAMS order by entered desc;

--
⚂⚃ 100% natural