> -----Mensaje original----- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki > Enviado el: Viernes, 10 de Octubre de 2008 07:56 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] SELECT multiple MAX(id)s ? > > Hello list, > > table diary_entry > > entry_id SERIAL PK > d_entry_date_time timestamp without time zone > d_entry_company_id integer d_entry_location_id integer > d_entry_shift_id integer d_user_id integer d_entry_header text ... > > Get the last entries from companies and their locations? > > The last, i.e. the biggest entry_id holds also the latest > date value within one company and its locations. One can not > add an entry before the previuos one is 'closed'. Names for > the companies, their different locations, or outlets if you > like, users and shifts are stored in company, location, user > and shift tables respectively. > > Again something I could do with a bunch of JOIN queries and > loops + more LEFT JOIN queries within the output loops, but > could this be done in a one single clever (sub select?) query? > > Output (php) should be something like: > > Date | User | Shift | Company | Location > --------------------------------------------------------- > > 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X > 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y > 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A > 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B > 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... > > Someone please give me a start kick? > > TIA and have a nice weekend too! > > -- > Aarni > > Burglars usually come in through your windows. >
Aarni, you should take a look at aggregate functions. Anyway, I think this is what you are asking for: select max(d.d_entry_date_time) as Date, u.name, s.shift, c.name, l.location_name from diary_entry d, company c, location l, user u, shift s where d.d_entry_company_id = c.company_id and d.d_entry_location_id = l.location_id and d.d_user_id = u.user_id and d.d_entry_shift_id = s.shift_id group by u.name, s.shift, c.name, l.location_name order by d.d_entry_date_time Cheers. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
