How to filter rows on a complex filter

Question

I have these rows in a table

ID  Name    Price   Delivery
==  ====    =====   ========
1   apple   1       1
2   apple   3       2
3   apple   6       3
4   apple   9       4
5   orange  4       6
6   orange  5       7

I want to have the price at the third delivery (Delivery=3) or the last price if there's no third delivery.

It would give me this :

ID  Name    Price   Delivery
==  ====    =====   ========
3   apple   6       3
6   orange  5       7

I don't necessary want a full solution but an idea of what to look for would be greatly appreciated.

Answer 1

SQL> create table t (id,name,price,delivery)
  2  as
  3  select 1, 'apple', 1, 1 from dual union all
  4  select 2, 'apple', 3, 2 from dual union all
  5  select 3, 'apple', 6, 3 from dual union all
  6  select 4, 'apple', 9, 4 from dual union all
  7  select 5, 'orange', 4, 6 from dual union all
  8  select 6, 'orange', 5, 7 from dual
  9  /

Table created.

SQL> select max(id) keep (dense_rank last order by nullif(delivery,3) nulls last) id
  2       , name
  3       , max(price) keep (dense_rank last order by nullif(delivery,3) nulls last) price
  4       , max(delivery) keep (dense_rank last order by nullif(delivery,3) nulls last) delivery
  5    from t
  6   group by name
  7  /

        ID NAME        PRICE   DELIVERY
---------- ------ ---------- ----------
         3 apple           6          3
         6 orange          5          7

2 rows selected.

EDIT: Since you want "an idea of what to look for", here is an description of why I think this solution is the best, besides being the query with the least amount of lines. Your expected result set indicates that you want to group your data per fruit name ("group by name"). And of each group you want to keep the values of the records with delivery = 3 or when that number doesn't exists, the last one ("keep (dense_rank last order by nullif(delivery,3) nulls last"). In my opinion, the query above just reads like that. And it uses only one table access to get the result, although my query is not unique in that.

Regards, Rob.

Answer 2

Use ROW_NUMBER twice - once to filter the rows away that are after the third delivery, and the second time to find the last row remaining (i.e. a typical max per group query).

I've implemented this using CTEs. I tested it in SQL Server but I believe that Oracle supports the same syntax.

WITH T1 AS (
    SELECT
        ID, Name, Price, Delivery,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Delivery) AS rn
    FROM Table1
), T2 AS (
    SELECT
        t1.*,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Delivery DESC) AS rn2
    FROM T1
    WHERE rn <= 3
)
SELECT ID, Name, Price, Delivery
FROM T2
WHERE rn2 = 1

Result:

ID  Name    Price  Delivery
3   apple   6      3       
6   orange  5      7