Postgres view with nested SQL query or how to find the last INET

Question

I have a query like so:

SELECT DISTINCT(orders.email), uuid_nil() AS customer_id, 'Order' AS customer_type, orders.first_name, orders.last_name, MAX(orders.paid_at) AS last_order_at, 1 AS order_count, SUM(orders.total_price_cents) AS total_spent_pennies
FROM orders
WHERE orders.state = 'paid' AND orders.customer_id IS null
GROUP BY orders.email, customer_id, orders.first_name, orders.last_name
UNION
SELECT DISTINCT(customers.email), customers.id AS customer_id, 'Customer' AS customer_type, customers.first_name, customers.last_name, MAX(orders.paid_at) AS last_order_at, COUNT(orders.*) AS order_count, SUM(orders.total_price_cents) AS total_spent_pennies
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.email, customers.id, customers.first_name, customers.last_name

Which looks like:

+-------------------------------+--------------------------------------+---------------+------------+--------------+-------------------------+-------------+---------------------+
| email                         | customer_id                          | customer_type | first_name | last_name    | last_order_at           | order_count | total_spent_pennies |
+-------------------------------+--------------------------------------+---------------+------------+--------------+-------------------------+-------------+---------------------+
| [email protected]                | 00000000-0000-0000-0000-000000000000 | Order         | Richard    | Doe          | 2015-12-18 14:45:22 UTC | 1           | 2000                |
| [email protected]                 | 00000000-0000-0000-0000-000000000000 | Order         | Paul       | Doe          | 2016-04-05 09:04:57 UTC | 1           | 5000                |
+-------------------------------+--------------------------------------+---------------+------------+--------------+-------------------------+-------------+---------------------+

My question is how can I include their last IP address also (INET column). One dates I can simply use the MAX aggregate function but IP address obviously doesn't have one.

Basically how can I combine this query above to give me a new column with their last_ip address with something like:

SELECT browser_ip FROM orders
WHERE email = '[email protected]'
ORDER BY paid_at DESC
LIMIT 1

Answer 1

You can probably get away with a simple subquery like so, you just need to name your queries in order to reference between.

http://www.techonthenet.com/postgresql/subqueries.php

For example the first part of your query would be something like:

SELECT DISTINCT(c1.email), c1.id AS customer_id, 'Customer' AS customer_type, 
c1.first_name, c1.last_name, MAX(orders.paid_at) AS last_order_at, 
COUNT(orders.*) AS order_count, SUM(orders.total_price_cents) AS total_spent_pennies, 
(SELECT browser_ip FROM orders WHERE c1.email = orders.email 
ORDER BY paid_at DESC LIMIT 1) last_ip
FROM customers c1
JOIN orders ON c1.id = orders.customer_id
GROUP BY c1.email, c1.id, c1.first_name, c1.last_name