-2

I have these record in table i want to fetch latest two record from this table( which is first 4 rows) using the timestamp

44121993    GEO     21-NOV-19 02.16.55.496000000 PM
44121993    PRODCT  21-NOV-19 02.16.55.496000000 PM
44121993    GEO     21-NOV-19 02.16.55.495000000 PM
44121993    PRODCT  21-NOV-19 02.16.55.495000000 PM
44121993    GEO     21-NOV-19 02.16.55.494000000 PM
44121993    PRODCT  21-NOV-19 02.16.55.494000000 PM
Haja Asmath
  • 1
  • 5

2 Answers2

1

After I have seen the tag "greatest-n-per-group", I would suggest using DENSE_RANK with PARTITION BY to fetch the first n records per group as following:

SELECT * FROM
    ( SELECT
          T.*,
           DENSE_RANK() OVER(
               PARTITION BY COL1 -- COL1 IS THE FIRST COLUMN FROM YOUR EXAMPLE 
                                 -- or COLUMN ON WHICH GROUPING NEED TO BE DONE
               ORDER BY TIMESTAMP_COL DESC) RN
        FROM  YOUR_TABLE T
    )
WHERE RN <= 2

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

You can use dense_rank():

select *
from (
    select 
        t.*,
        dense_rank() over(order by timestamp_col desc) rn
    from mytable t
) t
where rn <= 2
GMB
  • 216,147
  • 25
  • 84
  • 135