PostgreSQL: Querying the highest row in another table-CodePudding

I have three tables: Customer, CustomerOrder, and OrderStatus.

My database is filled with the following info:

Customer

id	name
1	Bob
2	James

CustomerOrder

id	customer	amount	status
1	1	100	1
2	1	83	1
3	1	432	2
4	2	58	3
5	2	33	2
6	3	10	1

OrderStatus

id	description
1	pending
2	completed
3	cancelled

I need help writing a SQL query which shows the status of the latest order (highest order id), per customer. Running the query on the data would produce the following result:

customer	latest_order_status
1	2
2	2
3	1

CodePudding user response：

Use max(CustomerOrder.Id) in combination with group by CustomerOrder.Customer to get highest OrderId per Customer.

CodePudding user response：

you can also use the windowing functions to order your orders by the customer in a descending order. The Row_number() function will assign the number 1 to the latest order.

SELECT *, ROW_NUMBER() over ( partition by customer_id order by id desc)  as row_no FROM

to have only the latest data you can nest this query like so:

SELECT
  *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY id DESC) AS row_no
  FROM
    orders )
WHERE
  row_no = 1

Here some more reading: postgreSQL row_number() function