Using PHP to show the latest X rows in an Oracle table

Question

I'm having a problem getting Oracle to return the latest X rows in a table (i.e. in this case it would be returning our newest orders).

$stid = oci_parse($conn, "
    SELECT OrderNo, InvoiceNo
    FROM Orders
    WHERE ROWNUM <= 5
    ORDER BY ROWNUM DESC
");

Reading this makes sense in that the highest row numbers should be first and it should show the first five. What am I doing wrong here?

check out http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering — VF_, Apr 25 '14 at 14:47
which of those fields is the primary key? You should be ordering by that field. — Marc B, Apr 25 '14 at 14:49
You asked for the lowest row numbers, not the highest. It just shows them in order from 5 to 1. — Barmar, Apr 25 '14 at 14:51
`ROWNUM` refers to the position in the database, not the result set. — Barmar, Apr 25 '14 at 14:52

score 0 · Answer 1 · answered Apr 25 '14 at 14:55

0

You just need to encapsulate your query in a subquery and you should be fine:

SELECT *
FROM
(
  SELECT OrderNo, InvoiceNo
  FROM Orders
  ORDER BY OrderNo, InvoiceNo DESC -- choose the correct sorting here
) 
WHERE ROWNUM <= 5

answered Apr 25 '14 at 14:55

Emmanuel

1 Answers1