Marrento Marrento - 7 months ago 11
SQL Question

SQL combining results

The query below return values of an 1 to many relationship between customers, addresses and plans

SELECT customers.name,
addresses.address,
plans.state,
plans.created,
plans.updated
FROM addresses INNER JOIN plans ON addresses.id = plans.address_id
INNER JOIN customers ON customers.id = addresses.customer_id


The query will return the data in the format bellow:

name | address | state | created | updated
-----------------------------------------------------------------------
Larry Smith | 123 St. | Active | 04/20/2016 | 04/20/2016

Larry Smith | 123 St. | Inactive| 03/20/2016 | 04/20/2016

Larry Smith | 123 St. | Inactive| 02/20/2016 | 03/20/2016

Jane Doe | 456 St. | Inactive| 03/20/2016 | 04/20/2016

Jane Doe | 456 St. | Inactive| 02/20/2016 | 03/20/2016


I want it to show the data where each customer are shown only once. The created field is the date of the first plan created and updated field is the update of the last plan and the state field is the state of the last plan

here is the example:

name | address | state | created | updated
-----------------------------------------------------------------------

Larry Smith | 123 St. | Active | 02/20/2016 | 04/20/2016

Jane Doe | 456 St. | Inactive| 02/20/2016 | 04/20/2016


I tried using Distinct or Group by on the address but since the plans have different IDs it will still show multiple entries per customers and also it does not address the requirement of combining the dates on the plan in a single record

EDIT
Clarification: I'm using Postgresql

I tried this query based on the queries provided by Gordon and Firetonton and some documentation on postgresql

SELECT customers.id, customers.name,
addresses.address,
FIRST_VALUE(plans.state) OVER w,
MIN(plans.created) as created_plan,
MAX(plans.updated) as cancelled_plan
FROM addresses
INNER JOIN plans ON addresses.id = plans.address_id
INNER JOIN customers ON customers.id = addresses.customer_id

WINDOW w as(
PARTITION BY plans.state ORDER BY plans.updated DESC
)

GROUP BY customers.id, customers.name, addresses.address, "plans".state


I'm getting
Error : ERROR: syntax error at or near "GROUP"


Thanks in advance

Answer

You have to use aggregate functions MIN, MAX and FIRST(... ORDER BY ...) in complement of GROUP BY :

SELECT customers.name, 
  addresses.address, 
  -- With "first" custom aggregate
  -- FIRST(plans.state ORDER BY plans.updated DESC), -- or ...plans.created... if it's your reference
  -- Or with postgres array_agg, makes an array an take the first value
  (array_agg(plans.state ORDER BY plans.updated))[1] AS last_state,
  MIN(plans.created), 
  MAX(plans.updated)
FROM addresses
 INNER JOIN plans ON addresses.id = plans.address_id
 INNER JOIN customers ON customers.id = addresses.customer_id
GROUP BY customers.name, addresses.address