Re: SELECT multiple MAX(id)s ?

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: <aarni(at)kymi(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: SELECT multiple MAX(id)s ?
Date: 2008-10-14 15:27:01
Message-ID: 0aab01c92e11$4ddb4ff0$8f01010a@iptel.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

> -----Mensaje original-----
> De: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Aarni Ruuhimäki
> Enviado el: Viernes, 10 de Octubre de 2008 07:56
> Para: pgsql-sql(at)postgresql(dot)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.

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Judith Altamirano 2008-10-14 22:34:53 effectiveness tool
Previous Message Denis Woodbury 2008-10-14 14:03:56 Re: EXISTS