Get rows based on max date and other filters from the table-CodePudding

I am using two filers from a table to get the data, i am getting two rows out of which i need to select the row with the max date. Anyone can suggest better way to get it as i am not getting any result from below


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|

CodePudding user response：

You want the max date from only the qualifying rows, you need to correlate the subquery:

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
);