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.
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 |