Results 1 to 2 of 2

Thread: Complex SQL

  1. #1
    Join Date
    Jan 2012

    Unanswered: Design help on complex SQL

    Hi there

    I hope someone could get me a help.

    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
    country, place
    CH, A
    CH, B
    CH, C
    AT, D
    AT, E
    FR, G

    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:
    SELECT r.from_place
    FROM routing r
    JOIN key_collection k ON k.order = r.order

    WHERE r.from_place IN ( SELECT place FROM country_places cp WHERE = 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 = 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 = k.from_country )

    Imaginge, that I have around 200'000 rows in my key_collection.

    I'm getting nuts .....

    Any help and ideas welcome.

    Thanks a lot

    P.S. Sysbase Adaptive Server Enterprise/12.5.4
    Last edited by prami; 01-25-12 at 10:40.

  2. #2
    Join Date
    Jan 2012

    no-one a tip how to make it faster & better ..... ?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts