locked
can someone plz Query RRS feed

  • 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

    Wednesday, 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