How to Efficiently Find the Latest Position for Each Security in a Database?-Mysql Tutorial-php.cn

How to Efficiently Find the Latest Position for Each Security in a Database?

Groupwise Maximum

The objective is to retrieve the latest position, identified by its ID and the corresponding buy date, for each unique security within a given dataset.

Consider the following table positions.

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

An optimized query that retrieves the desired results is as follows:

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;

Copy after login

This query avoids the use of sub-queries, which can significantly reduce execution time, particularly for large datasets.

The result is a table with the following structure:

</th></tr></thead>
<tbody>
<tr>
<td>id</td>
<td>security</td>
<td>buy_date</td>
</tr>
<tr>
<td>27</td>
<td>PCS</td>
<td>2013-01-19</td>
</tr>
<tr>
<td>31</td>
<td>RDN</td>
<td>2012-09-19</td>
</tr>
<tr>
<td>34</td>
<td>HK</td>
<td>2013-01-19</td>
</tr>
<tr>
<td>36</td>
<td>SGI</td>
<td>2013-02-16</td>
</tr>
<tr>
<td>18084</td>
<td>KERX</td>
<td>2013-02-20</td>
</tr>
</tbody>
</table>
<p>

Each row represents the latest position for a unique security, both in terms of ID and buy date.

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

source：php.cn

Previous article：How Can I Optimize Row Existence Checks in PostgreSQL? Next article：How to Handle Missing Database Records and Return a NULL Default Value in SQL?

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn