First I tried the following select
SELECT * FROM MESSAGE M where rownum <= 10 ORDER BY M.CREATE_DATE DESC;The above query does give me 10 rows, and it does sort them in descending order--just not the 10 rows that I want. The problem is that it simply returns the first 10 records in the database table (ordered by the table's primary key) and then sorts those 10 records in descending order.
To get what I really want, I need to put part of that select inside of another select, and part of it at the bottom of the new outer select, as shown below:
SELECT MESSAGE_ID, SUBJECT, CREATE_DATE FROM (SELECT * FROM MESSAGE M ORDER BY M.CREATE_DATE DESC )WHERE rownum <= 10;
In the second example, the select inside the parentheses is executed first, which gives me the data in descending CREATE_DATE (or most recent) order. After that, the query surrounding the parentheses is executed, which gives the MESSAGE_ID, SUBJECT, and CREATE_DATE of the first 10 of those rows.
No comments:
Post a Comment