Answered by:
can someone plz Query

Question
-
User-1738841732 posted
From the below table, how can i get the second last row, can someone plz Query
Col1 Col2 Col3 Col4
111 1 Insert 1/1/2020
222 1 Update 1/2/2020
333 1 Delete 1/3/2020
111 2 Insert 1/4/2020
222 2 Update 1/5/2020
333 2 Delete 1/6/2020
222 3 Cancel 1/7/2020
333 3 Update 1/8/2020
222 4 Delete 1/9/2020
333 4 Cancel 1/10/2020
Output
Col1 Col2 Col3 Col4
111 1 Insert 1/1/2020
222 3 Cancel 1/7/2020
333 3 Update 1/8/2020Wednesday, June 24, 2020 6:56 PM
Answers
-
User452040443 posted
Try something like this:
with CTE_RN as ( select *, row_number() over(partition by Col1 order by Col4 desc) as RN from MyTable ) select * from CTE_RN where RN = 2
Hope this help
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 24, 2020 7:34 PM -
User1535942433 posted
Hi mansooraabid,
Accroding to your description,as far as I think,you could orderby col2.
More details,you could refer to below codes:
;WITH cte AS (SELECT *, Row_number() OVER ( partition by Col1 order by Col2 desc) AS rn FROM Test6 ) --Omit date entries SELECT * FROM cte WHERE rn = 2
More details,you could refer to below article:
https://stackoverflow.com/questions/39082166/how-to-select-second-last-row-in-mysql
Best regards,
Yijing Sun
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 25, 2020 7:31 AM
All replies
-
User452040443 posted
Try something like this:
with CTE_RN as ( select *, row_number() over(partition by Col1 order by Col4 desc) as RN from MyTable ) select * from CTE_RN where RN = 2
Hope this help
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 24, 2020 7:34 PM -
User1535942433 posted
Hi mansooraabid,
Accroding to your description,as far as I think,you could orderby col2.
More details,you could refer to below codes:
;WITH cte AS (SELECT *, Row_number() OVER ( partition by Col1 order by Col2 desc) AS rn FROM Test6 ) --Omit date entries SELECT * FROM cte WHERE rn = 2
More details,you could refer to below article:
https://stackoverflow.com/questions/39082166/how-to-select-second-last-row-in-mysql
Best regards,
Yijing Sun
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 25, 2020 7:31 AM -
User-1738841732 posted
Tnks
Thursday, June 25, 2020 4:48 PM