I have to create an regular extract out of my SQL database.
The extract is collecting data from many different tables, as usual .. ;-)
What I do first is to collect the keys of data records I need to extract (already that is going over various tables with various criterias ..) into a temp table.
Nevertheless that's working well.
In next steps, I complete temp table by adding additional fields and filling them.
In one of this steps, I have now the problem, how to do it best ...
Let my try to explain.
I have a master table country_places like
Then I have a routing table, where I combine order items with place movements, like
order, item, movement sequence, from place, to place
order_1, 1, 1, A, B
order_1, 1, 2 B, D
order_1, 2, 1, A, B
order_1, 2, 2 B, D
order_2, 1, 1, A, G
What I want is to get
- the last place of same country where order starts
- even if item sequence not always starts with 1
- and even if movement sequence may vary from order item to order item
What I did so far is:
FROM routing r
JOIN key_collection k ON k.order = r.order
WHERE r.from_place IN ( SELECT place FROM country_places cp WHERE cp.country = k.from_country )
AND r.item = ( SELECT MIN(item)
FROM routing r2
WHERE r2.order = r.order
AND r2.from_place IN ( SELECT place from country_places cp WHERE cp.country = k.from_country )
AND r2.movement_sequence = ( SELECT MAX(movement_sequence)
FROM routing r3
WHERE r3.order = r2.order
AND r3.from_place IN ( SELECT place from country_places cp WHERE cp.country = k.from_country )
Imaginge, that I have around 200'000 rows in my key_collection.