SQL - Select rows with values greater than max value for id and category

Question

+------------+---------------+---------------+----------------+
| Product ID | Part Sequence | Part Material |  Description   |
+------------+---------------+---------------+----------------+
|          1 |             1 | Steel         | Part A         |
|          1 |             2 | CFK           | Part B         |
|          1 |             3 | CFK           | Part B Variant |
|          1 |             4 | Steel         | Part C         |
|          1 |             5 | GFK           | Part D         |
|          1 |             6 | Plastic       | Part E         |
|          2 |             1 | Steel         | Part A         |
|          2 |             2 | CFK           | Part B         |
|          2 |             3 | Steel         | Part F         |
|          2 |             4 | CFK           | Part B         |
|          2 |             5 | Steel         | Part G         |
|          2 |             6 | Silicon       | Part D+        |
|          2 |             7 | Plastic       | Part E         |
+------------+---------------+---------------+----------------+

(the ordering by Product ID and Part Sequence is only done for readability, my db table is unorderd)

I need to query all rows for each product id with a part sequence equal or higher to the last steel part.

So for the table above the expected result would be:

+------------+---------------+---------------+----------------+
| Product ID | Part Sequence | Part Material |  Description   |
+------------+---------------+---------------+----------------+
|          1 |             4 | Steel         | Part C         |
|          1 |             5 | GFK           | Part D         |
|          1 |             6 | Plastic       | Part E         |
|          2 |             5 | Steel         | Part G         |
|          2 |             6 | Silicon       | Part D+        |
|          2 |             7 | Plastic       | Part E         |
+------------+---------------+---------------+----------------+

I could use the solution from SQL Select only rows with Max Value on a Column FILTERED by Column to find the last steel part and then filter anything with a higher part sequence, but I'm hoping for a more efficient solution.

MT0 · Accepted Answer · 2015-06-25T07:15:29.660

Using a windowed aggregate function (requiring only a single pass over the table) rather than using a join (which will require two passes over the table):

[It will also return products where there are no steel parts - if this is an issue then a similar windowed query can be use to filter those rows out.]

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( Product_ID, Part_Sequence, Part_Material, Description ) AS
          SELECT 1, 1, 'Steel',   'Part A' FROM DUAL
UNION ALL SELECT 1, 2, 'CFK',     'Part B' FROM DUAL
UNION ALL SELECT 1, 3, 'CFK',     'Part B Variant' FROM DUAL
UNION ALL SELECT 1, 4, 'Steel',   'Part C' FROM DUAL
UNION ALL SELECT 1, 5, 'GFK',     'Part D' FROM DUAL
UNION ALL SELECT 1, 6, 'Plastic', 'Part E' FROM DUAL
UNION ALL SELECT 2, 1, 'Steel',   'Part A' FROM DUAL
UNION ALL SELECT 2, 2, 'CFK',     'Part B' FROM DUAL
UNION ALL SELECT 2, 3, 'Steel',   'Part F' FROM DUAL
UNION ALL SELECT 2, 4, 'CFK',     'Part B' FROM DUAL
UNION ALL SELECT 2, 5, 'Steel',   'Part G' FROM DUAL
UNION ALL SELECT 2, 6, 'Silicon', 'Part D+' FROM DUAL
UNION ALL SELECT 2, 7, 'Plastic', 'Part E' FROM DUAL
UNION ALL SELECT 3, 1, 'Silicon', 'Part A' FROM DUAL
UNION ALL SELECT 3, 2, 'Plastic', 'Part B' FROM DUAL;

Query 1:

SELECT Product_ID,
       Part_Sequence,
       Part_Material,
       Description
FROM   (
  SELECT t.*,
         COALESCE(
           SUM( CASE Part_Material WHEN 'Steel' THEN 1 ELSE 0 END )
             OVER ( PARTITION BY Product_ID
                    ORDER BY Part_Sequence
                    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ),
           0
         ) AS isInvalid
  FROM   TEST t
)
WHERE  isInvalid = 0

Results:

| PRODUCT_ID | PART_SEQUENCE | PART_MATERIAL | DESCRIPTION |
|------------|---------------|---------------|-------------|
|          1 |             4 |         Steel |      Part C |
|          1 |             5 |           GFK |      Part D |
|          1 |             6 |       Plastic |      Part E |
|          2 |             5 |         Steel |      Part G |
|          2 |             6 |       Silicon |     Part D+ |
|          2 |             7 |       Plastic |      Part E |
|          3 |             1 |       Silicon |      Part A |
|          3 |             2 |       Plastic |      Part B |

Pranay Rana · Answer 2 · 2015-06-25T08:18:11.243

2

Try below

Select * from product  
join 

(Select Max(Part_Sequence) as sequence,productid from product 
group by Productid, Part Sequence) d

on product.Part_Sequence = d.sequence
and product.productid = d.productid

if you want query for specific part just apply where clause to inner and outer query for that part

edited Jun 25 '15 at 08:18

answered Jun 25 '15 at 06:29

Pranay Rana

175,020
35
237
263

`product.[Part Sequence]` is an invalid identifier in SQL – Jun 25 '15 at 07:02

Madhivanan · Answer 3 · 2015-06-25T07:16:34.970

1

Try this

select t1.* from table as t1 inner join 
(
select product_id,max(Part_Sequence) as Part_Sequence from table as t 
where Part_Material='Steel'
group by product_id
)  t2 on t1.product_id=t2.product_id
where t1.Part_Sequence>=t2.Part_Sequence

edited Jun 25 '15 at 07:16

answered Jun 25 '15 at 06:30

Madhivanan

13,470
1
24
29

I thought it is SQL Server. Corrected it. Thanks :) – Madhivanan Jun 25 '15 at 07:16

score 1 · Answer 4 · answered Jun 25 '15 at 07:13

If the number of distinct product_id is low compared to the number of rows in the table, then using a join might perform well (otherwise the solution with a window function will be faster):

select p.*
from parts p
 join (
   select product_id, max(part_sequence) as max_seq
   from parts 
   where part_material = 'Steel'
   group by product_id
  ) t on t.product_id = p.product_id and p.part_sequence >= t.max_seq

Note that this will not return parts where there is no row with part_material = 'Steel'. I don't know if that is a possible scenario or not.

SQL - Select rows with values greater than max value for id and category

4 Answers4