Cheam Huoy San Cheam Huoy San - 5 months ago 16
SQL Question

SQL query to retrieve latest 2 week records

I have a database with CreatedDate is store in Unix epoch time and some other info. I want a query to able to retrieve latest 2 week record base on the last record.
Below is part of the example

ID User Ranking CreatedDate
-------------------------------------------------------
1 B.Sisko 1 1461136714
2 B.Sisko 2 1461123378
3 B.Sisko 3 1461123378
4 B.Sisko 3 1461600137
5 K.Janeway 4 1461602181
6 K.Janeway 4 1461603096
7 J.Picard 4 1461603096


The last record CreatedDate is 25 Apr 2016, so I want the record from 12 Apr to 25 Apr.

I not sure how to compare to get latest data? any suggestion

Answer

It may seem odd, but you need to execute two queries one to find the Maximum Date and knock off 14 days -- and then use that as a condition to requery the table. I used ID_NUM since ID is a reserved word in Oracle and likely other RDBMS as well.

    SELECT ID_NUM, USER, RANKING,
    TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((CreatedDate-18000)
    /(60*60*24)) GOOD_DATE
    FROM MY_TABLE
    WHERE
    GOOD_DATE >=
    (SELECT MAX( TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+
    ((CreatedDate-18000) /(60*60*24))) -14
    FROM MY_TABLE)