从表中获取基于最大日期和其他筛选器的行

Question

我使用来自一个表的两个文件处理程序来获取数据，我得到了两行数据，我需要选择具有最大日期的行。任何人都可以提出更好的方法来得到它，因为我没有从下面得到任何结果。

SELECT inv,account,activity,seq,st_date,open_amt,State,activity_date 
FROM table
WHERE inv = 'test_inv'
AND State = 'issued'
AND activity_date = (select MAX (activity_date) FROM table)


Added Table for more context, i need data based below two conditions
1. Max (activity_date) with Open_amt <> 0 and
2. Exclude rows if Max (activity_date) with Open_amt = '0' and State = 'closed'

| Inv #| Account #|Activity|State |Open_Amt|Last Activity Dt|Seq|St_date   |
| Inv #| Account #|Activity|State |Open_Amt|Last Activity Dt|Seq|St_date   |
| -----| ---------|--------|------|--------|----------------|---|----------|
| 123  | Customer1| Act-1  |Issued|12.50   |2022-02-18      |455|2022-01-04|
| 123  | Customer1| Act-2  |Closed|0.00    |2022-03-05      |567|2022-01-04|
| 345  | Customer2| Act-1  |Issued|15.00   |2022-02-18      |467|2022-01-12|
| 345  | Customer2| Act-2  |Issued|09.35   |2022-02-25      |488|2022-01-12|
| 678  | Customer3| Act-1  |Issued|30.50   |2022-03-20      |589|2022-01-23|
| 678  | Customer3| Act-2  |Closed|00.00   |2022-03-30      |623|2022-01-23|
| 678  | Customer3| Act-3  |Issued|30.50   |2022-04-02      |788|2022-01-23|
| 678  | Customer3| Act-4  |Issued|05.50   |2022-04-10      |988|2022-01-23|

for above table below is the output

| Inv #| Account #|Activity|State |Open_Amt|Last Activity Dt|Seq|St_date   |
| -----| ---------|--------|------|--------|----------------|---|----------|
| 345  | Customer2| Act-2  |Issued|09.35   |2022-02-25      |488|2022-01-12|
| 678  | Customer3| Act-4  |Issued|05.50   |2022-04-10      |988|2022-01-23|

Answer 1

如果只想从符合条件的行中获得最大日期，则需要关联子查询：

SELECT inv, account, activity, seq, st_date ,open_amt, State, activity_date 
FROM table t
WHERE inv = 'test_inv'
AND State = 'issued'
AND activity_date = (
  SELECT MAX (activity_date) 
  FROM table t2
  WHERE t2.inv = t.inv AND t2.State = t.State
);