Re: SELECT multiple MAX(id)s ? - Mailing list pgsql-sql

On Tuesday 14 October 2008 18:27:01 Fernando Hevia wrote:
> > -----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 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.

Thanks Fernando!

I will try this out. Although I already did it in the more clumsy way ...

Very best regards,

Aarni

--

Burglars usually come in through your windows.