Question 113850 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: Querying the highest row in another table

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
Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search