Highest Date Per Group

Another interesting question one of my students has sent me recently. Given the following Orders table. As you can see, this table has four columns : customer number, supplier number, order number, and order date.

Create a query to display the highest order date for each customer. For example, using the values shown above, the query will retrieve the following result:

You can use this script to create and populate the table


CREATE TABLE cust_orders
(custid int,
supid int,
orderid int,
orderdate datetime)

INSERT INTO cust_orders
VALUES (1,1,2,getdate() + 1)
INSERT INTO cust_orders
VALUES (1,2,3,getdate() + 2)
INSERT INTO cust_orders
VALUES (1,3,4,getdate() + 3)
INSERT INTO cust_orders
VALUES (1,4,5,getdate() + 4)
INSERT INTO cust_orders
VALUES (1,5,6,getdate() + 5)
INSERT INTO cust_orders
VALUES (1,6,7,getdate() + 6)
INSERT INTO cust_orders
VALUES (2,1,2,getdate() + 1)
INSERT INTO cust_orders
VALUES (2,2,3,getdate() + 2)
INSERT INTO cust_orders
VALUES (2,3,4,getdate() + 3)
INSERT INTO cust_orders
VALUES (2,4,5,getdate() + 4)
INSERT INTO cust_orders
VALUES (2,5,6,getdate() + 5)
INSERT INTO cust_orders
VALUES (2,6,7,getdate() + 6)umbers
VALUES (29)

Click here to see the solution

SELECT TabA.custid , TabA.supid , TabA.orderid, TabA.orderdate
FROM cust_orders TabA
JOIN
  (SELECT MAX(orderdate) AS orderdate , custid
  FROM cust_orders
  GROUP BY custid) as tabB
ON TabA.custid = tabB.custid AND TabA.orderdate = tabB.orderDate

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…

About Us

UpScale Analytics specializes in training solutions for organizations and individuals in the fields of data.

UpScale Analytics is also one of the largest platforms in the world for learning SQL by doing.

Highest Date Per Group

About Us

Popular Courses

Desicion Making with Basic SQL

PL/SQL Programming

Advanced Power BI

Quick Links