Retrieve last row with specific condition-CodePudding

Suppose we have a table like:

ID	Account	Amount	Date
1	4455	52	01-01-2022
2	4455	32	02-01-2022
3	4455	23	03-01-2022
4	4455	23	04-01-2022
5	6565	236	01-01-2022
6	6565	623	02-01-2022
7	6565	132	03-01-2022
8	2656	564	01-01-2022
9	2656	132	02-01-2022

We need to retrieve every last row of given account_no. We need output like:

ID	Account	Amount	Date
4	4455	23	04-01-2022
7	6565	132	03-01-2022
10	2656	13	03-01-2022

Kindly suggest me a query to retrieve data like this in table of 2000 records.

CodePudding user response：

You want the last row of certain query. So you must be having an order by clause. Just reverse the ordering and use a limit clause with limit set to one row.

SELECT column_name(s)
FROM table_name
WHERE condition
order by your_reversed_orderby_clause
LIMIT 1;

CodePudding user response：

If you are using MySQL 8, then you can use ROW_NUMBER() function for this:

WITH CTE AS 
(
  SELECT ID,Account,Amount,Date
   ,ROW_NUMBER() OVER(PARTITION BY Account ORDER BY ID DESC) AS RN 
  FROM Table1
)
SELECT * FROM CTE 
WHERE RN=1 
ORDER BY ID;

ID	Account	Amount	Date	RN
4	4455	23	2022-04-01 00:00:00	1
7	6565	132	2022-03-01 00:00:00	1
9	2656	132	2022-02-01 00:00:00	1

See this db<>fiddle

CodePudding user response：

SELECT * FROM table_name
WHERE ID IN (
  SELECT max(ID) FROM table_name
  GROUP BY Acount
  ORDER BY Account
)