How to Select Maximum Date Rows with Non-Zero Check Amounts in SQL?

Extracting Latest Entries with Non-Zero Check Values from a SQL Table

This guide demonstrates how to efficiently retrieve the most recent records for each group, filtering for non-zero check amounts. Let's assume a table structured like this:

<code>group    date      cash  checks
  1    1/1/2013     0      0
  2    1/1/2013     0      800
  1    1/3/2013     0      700
  3    1/1/2013     0      600
  1    1/2/2013     0      400
  3    1/5/2013     0      200</code>

The following SQL approach achieves this:

First, we find the maximum date for each group where the checks value is greater than zero:

<code class="language-sql">SELECT group, MAX(date) AS max_date
FROM table
WHERE checks > 0
GROUP BY group;</code>

This yields:

<code>group    max_date
  2    1/1/2013
  1    1/3/2013
  3    1/5/2013</code>

Next, we join this result back to the original table to retrieve the corresponding checks amounts:

<code class="language-sql">SELECT t.group, t.date AS max_date, t.checks
FROM table t
INNER JOIN (
  SELECT group, MAX(date) AS max_date
  FROM table
  WHERE checks > 0
  GROUP BY group
) AS a ON a.group = t.group AND a.max_date = t.date;</code>

This final query produces the desired output:

<code>group    max_date    checks
  2    1/1/2013    800
  1    1/3/2013    700
  3    1/5/2013    200</code>

Important Note: Using reserved words (like date) as column names is strongly discouraged. It can lead to SQL syntax errors and make your code harder to maintain. Consider more descriptive and unambiguous names for your columns.

The above is the detailed content of How to Select Maximum Date Rows with Non-Zero Check Amounts in SQL?. For more information, please follow other related articles on the PHP Chinese website!