0

I'm dealing with a legacy database with about 2 million rows. It's used for tracking order information. It's basically a transactional log of order entry and all edits, cancellations, etc., to that order. This database comes from a legacy 1980s error text database program that is rock solid. I'm making some web views of the data.

Simplified schema:

id Order# Order Line# Widget ID# Quantity ~ 15 other columns
1 100 1 ABC 5
2 100 2 DEF 2
3 101 1 XYZ 10
4 102 1 ABC 5
5 100 1 ABC -5
6 100 1 GHI 2

The relevant part is that in lines id 1 and 2:

  1. id 1: defines Order #100, Order Line 1, widget "ABC", in quantity of 5
  2. id 2: defines Order #100, Order Line 2, widget "DEF" in quantity 2.

Then, id 5, Order Line 1 is cancelled.

  1. id 5: defines Order #100, Order Line 1, Widget "ABC", adds quantity -5, zeroing out the earlier order of 5 so effectively cancelling Order #100, Order Line 1.

Then, a NEW widget replaces the original, and now zeroed out, Order #100 / Order Line 1.

  1. id 6, Order #100, Order Line #1 is replaced by an order of Widget "GHI" with quantity 2.

It's simple to deal with this data structure on an order by order basis, but I was hoping to create a VIEW that would remove some of the application logic.

Basically what I need is a query that GROUPs BY:

  1. Order #
  2. Order Line #

And returns the entire row with the highest id. So the above table as a VIEW would remove rows 1 and 5 since they cancel each other out and are superseded by row id 6. Like:

id Order# Order Line# Widget ID# Quantity ... other columns ...
2 100 2 DEF 2
3 101 1 XYZ 10
4 102 1 ABC 5
6 100 1 GHI 2

Orders can comprise 100s of rows and have multipe edits, but I just want the most recent (highest id) for each order# - order line # grouping if that makes sense.

Edit: Actually this is even more complicated that I realized, because it's not always a total replacement as in my contrived example. Sometimes a new quantity can be added, in which case it has to be summed from multiple rows.

Improve this question
2
  • There is nothing that distinguishes between "replacing DEF by GHI" versus "also ordering GHI". I would read your table as indicating the latter. Please clarify. Commented Feb 18, 2023 at 18:30
  • I've tried to make it a bit more clear. The data structure is basically a transactional log of order entry and any edits or cancellations made to that order. Items can be removed, items can be replaced, quantities can be modified, etc. I'm playing around with seeing if I can do a GROUP BY that SUMs() quantity and ignores all zero quantities. That might work. Commented Feb 18, 2023 at 18:54

1 Answer 1

Reset to default
1

Ok, I was dramatically overthinking this, and Rick James' comment pointed me in the right direction.

SELECT * FROM order_log 
  GROUP BY order_number, order_line, widget_id
  HAVING SUM(quant) > 0 
  ORDER BY order_line
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.