skip to Main Content

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

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search