SQL query for extracting distinct records by getting the last entered row

Sabi 0
Viewed 40

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

table

I want to show result like this

result

mysql sql
2 Answers

On MySQL 8+, we can use ROW_NUMBER here:

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.

Tim Biegeleisen0

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
lucumt0
Related