Embarassing GROUP question

From: Corey Tisdale <corey(at)eyewantmedia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Embarassing GROUP question
Date: 2009-10-03 16:25:14
Message-ID: 36AA710D-69ED-4C8D-8F79-C3E69DB36C0E@eyewantmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'm a recent MySQL convert, and I am having some difficulty with
syntax on grouping a table by a foreign key and returning only the
newest entry that matches. In MySQL, you can do something like

event_log
------------
id
event_type
event_date
meaningful_data

SELECT
meaningful_data,
event_type,
event_date
FROM
event_log
GROUP BY
event_type
ORDER BY
event_date DESC

And this would return back the most recent event and meaningful data
for each event type. When I try this in postgres, I get errors about
either grouping by id or using id in an aggregate function. I am
afraid if I use it in an aggregate function, it will disrupt the sort
order I am attempting, and if I group by it, I no longer get the most
recent data for each event type, but I get every event. How would I
accomplish this is postresql? Is there some aggregate functions that
help with this, or should I be trying a different method altogether?
Grouping feels pretty different, and I suspect closer to standards, in
postgres. Unfortunately, my mind works (for the short term) in mysql.
Will someone please give me some pointers?

Thanks!
Corey

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2009-10-03 16:32:39 Re: Procedure for feature requests?
Previous Message Sam Mason 2009-10-03 16:19:49 Re: How useful is the money datatype?