Is there a more efficient way to aggregate the top row from multiple select statements into a single result?

Question

Ok, I have experience in various databases, but not Oracle specifically, so still trying to wrap my head around how it handles things (i.e. the lack of 'Top' drives me mad!) Anyway, here's what we're trying to do...

Note: We're using Oracle 11. I understand a new feature was added to Oracle 12 (i.e. FETCH NEXT n ROWS ONLY) but we can't use that unfortunately.

To start, we have an articles table (technically it's a view, but I'm simplifying it here). Here are the relevant fields...

• ID (Integer, Primary Key)
• Title (String)
• Body (String)
• IsFavorite ('Y' or 'N')
• IsFeatured ('Y' or 'N')
• DatePublished (Date)

Multiple articles can be marked as favorites and/or featured.

What we want to return is a result set that has the following, in order...

1. The most-recently published article with IsFeatured = 'Y', if any.
2. The most-recently published article with IsFavorite = 'Y' that's not the row from #1 (this avoids duplicates if the most recent featured is also the most recent favorited and instead selects the next favorited row) if any
3. The three most-recently published articles that are not #1 or #2, if any

Here's what I've come up with so far (cut/pasted/edited here so there may be some typos), but this just feels so 'clunky' to me... like I'm wasting a lot of unnecessary processing and iterations.

WITH mostRecentlyFeatured as (
    Select * from (
        Select 2 as MAJOR_SORT, A.*
        From Articles A
        where IsFeatured = 'Y'
        order by DatePublished DESC
    )
    where ROWNUM = 1
),
mostRecentlyFavorited as (
    Select * from (
        Select 1 as MAJOR_SORT, A.*
        From Articles A
        minus Select * From mostRecentlyFeatured
        where IsFavorite = 'Y'
        order by DatePublished DESC
    )
    where ROWNUM = 1
),
topThreeOthers as (
    Select * from (
        select 0 as MAJOR_SORT, A.*
        from Articles
        minus
        SELECT * from mostRecentlyFeatured
        minus
        SELECT * from mostRecentlyFavorited
        order by DatePublished desc
    )
    where ROWNUM <= 3
),
finalRows as (
    Select * from mostRecentlyFeatured
    union all
    Select * from mostRecentlyFavorited
    union all
    select * from topThreeOthers
)
Select * from finalRows
Order By MAJOR_SORT    DESC,
         DatePublished DESC;

This isn't the most uncommon of queries so I can't imagine there isn't a better way to do this, but I'm just not yet seeing it. So is there?

Answer 1

Here's a possible solution to your query. It uses a set of CTEs to select the first n featured articles, the first m favourite articles (that are not in the first n featured articles) and a total of k articles:

• the first generates row numbers for featured and favourite articles;
• the second counts how many of the first n featured articles are also int the first m favourites; and
• the third generates a row number for articles sorted based on first n featured, first m favourites (unless they were also amongst the first n featured articles, in which case the next favourites are chosen) and date published.

Finally the top k rows are selected from the last CTE.

Here's a query where n = 2, m = 3 and k = 8:

-- first 2 (n) featured, first 3 (m) favourites, then 3 others (8 (k) total)
WITH favfeatrank AS (
  -- rank articles by date and whether they are favourite or featured
  SELECT ID, Title, IsFavorite, IsFeatured, DatePublished,
         ROW_NUMBER() OVER (ORDER BY IsFeatured DESC, DatePublished DESC) feat,
         ROW_NUMBER() OVER (ORDER BY IsFavorite DESC, DatePublished DESC) fav
  FROM Articles
),
numfavfeat AS (
  -- number of favourites that are also in the first n featured
  -- use m in the next line
  SELECT COUNT(CASE WHEN fav <= 3 THEN 1 END) AS featfav
  FROM favfeatrank
  WHERE feat <= 2 -- use n here
), 
articlerank AS (
  -- articles ranked according to featured, favourite and date published
  SELECT ID, Title, IsFavorite, IsFeatured, DatePublished,
         ROW_NUMBER() OVER (ORDER BY CASE WHEN feat <= 2 THEN 0 -- use n here
                                          WHEN fav <= 3 + featfav THEN 1 -- use m here
                                          ELSE 2
                                          END,
                                      DatePublished DESC) AS rn

  FROM favfeatrank
  CROSS JOIN numfavfeat
)
SELECT ID, Title, IsFavorite, IsFeatured, DatePublished
FROM articlerank 
WHERE rn <= 8 -- use k here

Demo on dbfiddle

Answer 2

the lack of 'Top' drives me mad

Since you are on Oracle 11g, TOP-n is not supported. So ROWNUM is the only way to go. See How ROWNUM works in pagination query.

For example, an OFFSET of 4 and FETCH next 4 using ROWNUM:

SQL> SELECT val
     FROM   (SELECT val, rownum AS rnum
            FROM   (SELECT val
                    FROM   order_test
                    ORDER BY val)
            WHERE rownum <= 8)
     WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

From Oracle 12c on ward, you could use the Top-n row limiting feature. Since you haven't provided any sample data, here is a simple demo:

SQL> select * from order_test order by val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

First 4 rows :

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  FETCH FIRST 4 ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

Next 4 rows(look at OFFSET) :

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

Finally, next 4 rows with OFFSET 8 rows :

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  OFFSET 8 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         5
         5
         6
         6

Using the above approach, you could get the most-recently published article marked with 'IsFeatured' as follows:

SELECT * FROM articles
WHERE isfeatured = true
ORDER BY datepublished DESC
FETCH FIRST ONE ROW ONLY;