SQL query for extracting last entered number field(if we enter 11 in 3 rows take last row) from a table.Consider the following table
I want to show result like this
2
On MySQL 8+, we can use ROW_NUMBER here:
ROW_NUMBER
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY NO ORDER BY created_date DESC) rn FROM yourTable ) SELECT NO, Letter FROM cte WHERE rn = 1;
The above would return the latest row for every NO group. If instead you want the earliest row, then change the sort order used by ROW_NUMBER.
NO
Another option for you
SELECT a.NO,a.Letter FROM yourtable a JOIN (SELECT max(date) AS date,NO FROM yourtable GROUP BY NO) b ON a.NO=b.NO AND a.DATE=b.DATE ORDER BY a.NO
Click here to cancel reply.
2
Answers
On MySQL 8+, we can use
ROW_NUMBER
here:The above would return the latest row for every
NO
group. If instead you want the earliest row, then change the sort order used byROW_NUMBER
.Another option for you