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/2020

Answer 1

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

Answer 2

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

Answer 3

User-1738841732 posted

Tnks