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
Asked
Active
Viewed 41 times
-2
-
The input and output are identical – Tim Biegeleisen Sep 12 '22 at 06:00
-
soryy,edited the uestion – Sabi Sep 12 '22 at 06:01
-
1does the table has a timestamp column? – Abhishek Kumar Sep 12 '22 at 06:06
-
yes, created date will there – Sabi Sep 12 '22 at 06:14
-
Can you tag the version of the database please – Barbaros Özhan Sep 12 '22 at 06:18
-
you can use this query ````select * from tableName group by no order by created desc;```` – Tushar Sureka Sep 12 '22 at 06:23
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.
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
1
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
flyingfox
- 13,414
- 3
- 24
- 39