Search code examples
sqloracle-database

Oracle SQL last n records

i have read tons of articles regarding last n records in Oracle SQL by using rownum functionality, but on my case it does not give me the correct rows.

I have 3 columns in my table: 1) message (varchar), mes_date (date) and mes_time (varchar2).

Inside lets say there is 3 records:

Hello world | 20-OCT-14 | 23:50
World Hello | 21-OCT-14 | 02:32
Hello Hello | 20-OCT-14 | 23:52

I want to get the last 2 records ordered by its date and time (first row the oldest, and second the newest date/time)

i am using this query:

SELECT * 
  FROM (SELECT message 
          FROM messages 
         ORDER 
            BY MES_DATE, MES_TIME DESC
       ) 
  WHERE ROWNUM <= 2 ORDER BY ROWNUM DESC;

Instead of getting row #3 as first and as second row #2 i get row #1 and then row #3

What should i do to get the older dates/times on top follow by the newest?

Solution

  • Maybe that helps:

    SELECT * 
  FROM (SELECT message,
               mes_date,
               mes_time,
               ROW_NUMBER() OVER (ORDER BY TO_DATE(TO_CHAR(mes_date, 'YYYY-MM-DD') || mes_time, 'YYYY-MM-DD HH24:MI') DESC) rank
          FROM messages 
       ) 
  WHERE rank <= 2
  ORDER 
     BY rank