How to filter strings 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 a price on the third delivery (Delivery = 3) or the last price if there is no third delivery.

This will give me this:

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

I do not need a complete solution, but the idea of ​​what to look for will be greatly appreciated.

Answer 1

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

I implemented this with CTE. 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

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. " , ", , , , , . , ( " " ). = 3, , ( "keep (dense_rank last order by nullif (, 3) nulls last" ). , . , .

, .

Answer 3

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 4

Mark APC , , . , = 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;

Oracle XE 10g:

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

I included a third example in the sample data to illustrate the impact of different interpretations of the question. Other solutions would choose ID = 9 for the pear.