Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pulling out the first of each type in a dataset

    hi all

    what I want to do is kind of like a distinct statement, but not quite

    i have a table called 'delivery_brackets' which I use to determine the cost of something given its weight, along the lines of

    "SELECT * FROM delivery_brackets WHERE delivery_brackets_endofRange > " & totalWeight

    where the first entry returned would be the cost- the table is set up with price ascending.

    thing is I want the first entry for each delivery_brackets_delivery_id, where the delivery_id is from a linked table, like id 1 is express post, 2 is courier etc

    so yeah, what i want is to use the totalWeight, and pull out a table like
    1, expresspost, $12
    2, courier, $14
    3, localpost, $5

    with out all the duplicates.

    .. if that makes any sense lol

    thanks,

    James

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    SELECT i.id, i.name, min(b.price) FROM delivery_items i, price_brackets b WHERE b.enf_of_range>i.weight GROUP BY i.id, i.name;

    Should be close...


  •  

    Posting Permissions

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