Re: SELECT multiple MAX(id)s ?

> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] 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 swhere d.d_entry_company_id = c.company_id
andd.d_entry_location_id = l.location_id  and d.d_user_id = u.user_id  and d.d_entry_shift_id = s.shift_idgroup by
u.name,s.shift, c.name, l.location_nameorder by d.d_entry_date_time 

Cheers.