Clever Geek Handbook

SQL - select rows with values ​​exceeding the maximum value for id and category

+------------+---------------+---------------+----------------+
| 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         |
+------------+---------------+---------------+----------------+

(ordering by product id and partial sequence is read-only, my db table is unordered)

I need to query all rows for each product ID with a sequence of parts equal to or greater than the last steel part.

So, for the table above, the expected output is:

+------------+---------------+---------------+----------------+
| 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 the rows with the maximum value in the FILTERED by Column to find the last steel part and then filter out anything with the higher part but I hope for a more efficient solution.

+3


source to share


4 answers


Using a windowed aggregate function (requiring only one pass over the table) instead of using a join (which requires two passes over the table):

[It will also return products that have no steel parts - if that's a problem, then a similar window query can be used to filter those rows.]

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;


Request 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 |
+4


source


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 a query for a specific part just apply a where clause to the inner and outer query for that part

+2


source


try it

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
+1


source


If the number of distincts is product_id

less than the number of rows in the table, then using a join might work well (otherwise the window function solution 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 line with part_material = 'Steel'

. I don't know if this is a possible scenario or not.

+1


source






More articles:


All Articles