Search code examples
sqloracle-databasedatejoinrownum

SELECT 4 latest rows from Oracle table with join

I'm quite new to Oracle so I'm not totally familiar with the ROWNUM statement. I'm trying to get the latest 4 articles from my table. I'm getting 4 results but they are 2012 articles even though my date ordering is set to DESC. Any help would be great.

Oracle query:

SELECT bt.article_id, ba.* 
FROM articles_types bt 
LEFT JOIN blog_articles ba 
ON ba.article_id = bt.article_id 
WHERE ROWNUM < 5 
ORDER BY Published DESC
Solution

  • Just a wild guess, but order the result before rownum limit:

    select t.* from
(
SELECT * 
FROM articles_types bt 
LEFT JOIN blog_articles ba 
ON ba.article_id = bt.article_id 
ORDER BY Published DESC
) T
WHERE ROWNUM <= 4

    This worked, the issue was a duplicate column name