How to Find the Most Recent Position and ID for Each Security in a Database?

Groupwise Maximum

Problem:

Retrieve the latest position and its corresponding ID for each security in a table containing multiple positions with the same security.

Data Table:

id	security	buy_date
26	PCS	2012-02-08
27	PCS	2013-01-19
28	RDN	2012-04-17
29	RDN	2012-05-19
30	RDN	2012-08-18
31	RDN	2012-09-19
32	HK	2012-09-25
33	HK	2012-11-13
34	HK	2013-01-19
35	SGI	2013-01-17
36	SGI	2013-02-16
18084	KERX	2013-02-20
18249	KERX	0000-00-00

Solution:

Using a LEFT JOIN and NULL-filtering:

SELECT p1.id,
       p1.security,
       p1.buy_date
FROM positions p1
LEFT JOIN positions p2
    ON p1.security = p2.security
    AND p1.buy_date < p2.buy_date
WHERE
    p2.id IS NULL;

In this query, we use a LEFT JOIN to merge the positions table with itself, matching rows by security. The condition p1.buy_date < p2.buy_date ensures that p2 represents a more recent position for the same security.

The WHERE clause then filters out any rows where a more recent position exists, leaving us with only the latest position for each security.

Results:

id	security	buy_date
27	PCS	2013-01-19
31	RDN	2012-09-19
34	HK	2013-01-19
36	SGI	2013-02-16
18084	KERX	2013-02-20

The above is the detailed content of How to Find the Most Recent Position and ID for Each Security in a Database?. For more information, please follow other related articles on the PHP Chinese website!