postgres - group by on multiple columns - master/detail type table

Posted by smpillay on Stack Overflow See other posts from Stack Overflow or by smpillay
Published on 2010-05-21T19:27:52Z Indexed on 2010/05/21 19:30 UTC
Read the original article Hit count: 188

Filed under:

postgresql

|

group-by

I have a table order(orderid, ordernumber, clientid, orderdesc etc.,) and a corresponding status for that order on an order_status table ( statusid, orderid, statusdesc, statusNote, statustimestamp)

say I have a record in order as below

orderid     orderumber     clientid    orderdesc
1111        00980065       ABC         blah..blah..

and a corresponding status entries

statusid   orderid       statusdesc    statusNote    statustimestamp
11         1111          recvd         status blah   yyyy-mm-dd:10:00
12         1111          clientproce   status blah   yyyy-mm-dd:11:00
13         1111          clientnotice  status blah   yyyy-mm-dd:15:00
14         1111          notified      status blah   yyyy-mm-dd:17:00

How can I get the following result (latest timestamp along with multiple columns)

1111  14  00980065 ABC  blah..blah..  notified  status blah yyyy-mm-dd:17:00

© Stack Overflow or respective owner

Related posts about postgresql

Postgresql fails to start on Ubuntu 10.04.4 LTS

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I installed postgresql 9.2 from add-apt-repository ppa:pitti/postgresql using apt-get install postgresql-9.2 At the end of the install and every time I try to launch postgresql by using the following command /etc/init.d/postgresql start or service postgresql start I get this error: Error:… >>> More
can't install psycopg2 in my env on mac os x lion

as seen on Server Fault - Search for 'Server Fault'
I tried install psycopg2 via pip in my virtual env, but got this error: ld: library not found for -lpq (full log here: http://pastebin.com/XdmGyJ4u ) I tried install postgres 9.1 from .dmg and via port, (gksks)iMac-Alexander:~ lorddaedra$ locate libpq /Developer/SDKs/MacOSX10.7.sdk/usr/include/libpq /Developer/SDKs/MacOSX10… >>> More
Postgresql has broken apt-get on Ubuntu

as seen on Super User - Search for 'Super User'
On ubuntu 12.04, whenever I try to install a package using apt-get I'm greeted by: The following packages have unmet dependencies: postgresql-9.1 : Depends: postgresql-client-9.1 but it is not going to be instal led E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a so lution)… >>> More
Installing PostgreSQL on FreeBSD (with ports)

as seen on Server Fault - Search for 'Server Fault'
Hey everyone, I am trying to install (using ports) PostgreSQL on a virtual server, running FreeBSD. My one question is this: Which of the following should I install? postgresql-contrib postgresql-docs postgresql-jdbc postgresql-libpgeasy postgresql-libpq++ postgresql-libpqxx postgresql-odbc … >>> More
Strange permission errors in new PostgreSQL installation

as seen on Server Fault - Search for 'Server Fault'
A freshly installed PostgreSQL (with configuration overwritten) won't start: $ sudo service postgresql start * Starting PostgreSQL 9.1 database server * Error: could not read /etc/postgresql/9.1/main/postgresql.conf: Permission denied Looks like it should be able to read it though: $ ls -l postgresql… >>> More

Related posts about group-by

Twitter Bootstrap styling conflicts with plug-ins like jqGrid and other third part libraries

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Issues:The concern is that the Twitter Bootstrap framework is that some of their css selectors are simply too generic and have incompatibility issues and conflicts with most third party plug-ins and css libraries, like jQuery-UI and jqGrid.My most pressing concern is only with the generic selector… >>> More
ASA hairpining: I basicaly want to allow 2 spokes to be able to communicate with each other.

as seen on Server Fault - Search for 'Server Fault'
ASA Spoke to Spoke Communication I have been looking at spke to spoke comms or "hairpining" for months and have posted on numerouse forums but to no avail. I have a Hub and spoke network where the HUB is an ASA Firewall version 8.2 * I basicaly want to allow 2 spokes to be able to communicate with… >>> More
Using net group to add users to a AD group

as seen on Server Fault - Search for 'Server Fault'
Hi, I'm trying to add users to the an Active Directory group using net group. We use Domain Local groups for everything. When I go to run the command net group "group name" "username" /add /domain it returns "The group name could not be found. More help is available by typing NET HELPMSG 2220." If… >>> More
rsync set group owner, group permission

as seen on Super User - Search for 'Super User'
I want to use rsync to transfer files from my computer to a remote Linux system. Regardless of the local file's group ownership, I want to set these values on the remote side. If I was on the remote Linux system, I could create the directory and set the ownership and permissions as: mkdir my_directory chown… >>> More
Specify which row to return on SQLite Group By

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm faced with a bit of a difficult problem. I store all the versions of all documents in a single table. Each document has a unique id, and the version is stored as an integer which is incremented everytime there is a new version. I need a query that will only select the latest version of each document… >>> More