How to use Analytic Functions in this query

Question

I have a very unique query that I must write, and hoping to do it all in 1 query, but not sure if I can.

I have a list of Articles, each article has a unique ID. The application passes this ID to the stored procedure. Then, I am to retrieve that article, AND, the next & previous articles. So, the list is sorted by date, and I can get the next & previous.

I do this via LEAD & LAG. It works in one query. However, in some cases, that next or previous article has a NULL in one of the fields. If that field is NULL, I am basically to get the next article where that field is NOT NULL.

Then there is one more thing. The article passed from the application has a category assigned to it. The next & previous articles must be of the same category.

The query is pretty big now, but it work getting the next & previous to the article ID passed as the subquery sorts everything by date. But with these 2 new criteria, the NULL factor and category factor, I do not see how it is possible to do this in one query.

Any thoughts? Or need some examples, or my existing query?

Thanks for all your time.

MT0 · Answer 1 · 2017-06-02T01:33:37.493

Oracle Setup:

CREATE TABLE articles ( id, category, value, dt ) AS
  SELECT 1, 1, 1,    DATE '2017-01-01' FROM DUAL UNION ALL
  SELECT 2, 1, 2,    DATE '2017-01-02' FROM DUAL UNION ALL -- Previous row
  SELECT 3, 1, NULL, DATE '2017-01-03' FROM DUAL UNION ALL -- Ignored as value is null
  SELECT 4, 1, 1,    DATE '2017-01-04' FROM DUAL UNION ALL -- Chosen id
  SELECT 5, 2, 3,    DATE '2017-01-05' FROM DUAL UNION ALL -- Ignored as different category
  SELECT 6, 1, 5,    DATE '2017-01-06' FROM DUAL;          -- Next row

Query:

SELECT *
FROM   (
SELECT a.*,
       LAG( CASE WHEN value IS NOT NULL THEN id END ) IGNORE NULLS OVER ( PARTITION BY category ORDER BY dt ) AS prv,
       LEAD( CASE WHEN value IS NOT NULL THEN id END ) IGNORE NULLS OVER ( PARTITION BY category ORDER BY dt ) AS nxt
FROM   articles a
)
WHERE :your_id IN ( id, nxt, prv )
AND   ( id = :your_id OR value IS NOT NULL )
ORDER BY dt;

(:your_id is set to 4 in the example output below.)

Output:

        ID   CATEGORY      VALUE DT                         PRV        NXT
---------- ---------- ---------- ------------------- ---------- ----------
         2          1          2 2017-01-02 00:00:00          1          4
         4          1          1 2017-01-04 00:00:00          2          6
         6          1          5 2017-01-06 00:00:00          4

How to use Analytic Functions in this query

1 Answers1