SQL - Pull distinct row based on max value

Question

I am trying to pull the most recent sale amount for each salesperson. The salespeople have made a sale on multiple days, I only want the most recent one.

My attempt below:

SELECT salesperson, amount
FROM table
WHERE date = (SELECT MAX(date) FROM table);

SELECT * FROM ( SELECT SalesPerson,Amount, Row_NUMBER() OVER (Partition by SalesPerson ORDER BY Date DESC) as rn FROM table ) WHERE rn = 1 — Rajesh, Oct 01 '19 at 14:51
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Oct 01 '19 at 14:56

score 2 · Accepted Answer · answered Oct 01 '19 at 14:43

2

Use correlated subquery :

SELECT t.salesperson, t.amount
FROM table t
WHERE t.date = (SELECT MAX(t1.date) 
                FROM table t1 
                WHERE t1.salesperson = t.salesperson -- for each salesperson
               );

answered Oct 01 '19 at 14:43

Yogesh Sharma

49,870
5
26
52

That works- Many thanks. Can you please provide a resource to explain the t, t1 portion? Is there another way to do this? – franklingranger Oct 01 '19 at 14:51

score 0 · Answer 2 · answered Oct 01 '19 at 14:55

If you are using PostgreSQL, you can take advantage of DISTINCT ON:

SELECT DISTINCT ON (salesperson) salesperson, amount
FROM table t
ORDER BY salesperson, date DESC

This will return only one row for each salesperson. The ORDER BY clause says to return the one with the largest date for that salesperson.

Unfortunately, DISTINCT ON is not supported by other databases.

SQL - Pull distinct row based on max value

2 Answers2