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