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

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       

Answer 2

select t3.ID, t3.Name, t3.Price, t3.Delivery
from  (
    select Name, max(Delivery) as MaxDelivery
    from MyTable
    group by Name
) t1
left outer join MyTable t2 on t1.Name = t2.Name and Delivery = 3
inner join MyTable t3 on t1.Name = t3.name 
    and t3.Delivery = coalesce(t2.Delivery, t1.MaxDelivery)

Answer 3

Mark's and APC's answers work if you meant the third delivery, regardless of the Delivery number. Here's a solution using analytic functions that specifically searches for a record with Delivery = 3.

CREATE TABLE FRUITS (
    ID NUMBER,
    Name VARCHAR2(10),
    Price INTEGER,
    Delivery INTEGER);

INSERT INTO FRUITS VALUES (1, 'apple', 1, 1);
INSERT INTO FRUITS VALUES (2, 'apple', 3, 2);
INSERT INTO FRUITS VALUES (3, 'apple', 6, 3);
INSERT INTO FRUITS VALUES (4, 'apple', 9, 4);
INSERT INTO FRUITS VALUES (5, 'orange', 4, 6);
INSERT INTO FRUITS VALUES (6, 'orange', 5, 7);
INSERT INTO FRUITS VALUES (7, 'pear', 2, 5);
INSERT INTO FRUITS VALUES (8, 'pear', 4, 6);
INSERT INTO FRUITS VALUES (9, 'pear', 6, 7);
INSERT INTO FRUITS VALUES (10, 'pear', 8, 8);

SELECT ID,
       Name,
       Price,
       Delivery
  FROM (SELECT ID,
               Name,
               Price,
               Delivery,
               SUM(CASE WHEN Delivery = 3 THEN 1 ELSE 0 END) 
                   OVER (PARTITION BY Name) AS ThreeCount,
               ROW_NUMBER()
                   OVER (PARTITION BY Name ORDER BY Delivery DESC) AS rn
          FROM FRUITS)
 WHERE (ThreeCount <> 0 AND Delivery = 3) OR
       (ThreeCount = 0 AND rn = 1)
ORDER BY ID;

DROP TABLE FRUITS;

And the results from Oracle XE 10g:

ID   Name       Price   Delivery  
---- ---------- ------- ----------
3    apple      6       3         
6    orange     5       7         
10   pear       8       8         

I included a third fruit in the sample data to illustrate the effect of different interpretations of the question. The other solutions would pick ID=9 for the pear.

Answer 4

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.