Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Group by with order by?

    I have a table called 'purchases' which has about 200,000 entries:
    item_number - varchar(32)
    vendor_id - integer
    warehouse - integer
    price - float
    month - integer
    year - integer

    Code:
    INSERT INTO purchases VALUES
    ('5555BLUE', 3345812, 15, 1.5738, 201, 5), // Feb 1, 2005
    ('5555BLUE', 3345812, 15, 1.844, 1015, 5), // Oct 15, 2005
    ('5555BLUE', 3345812, 15, 1.9, 1205, 5), // Dec 1, 2005
    ('1111WHITE', 2876333, 3, 2.5, 301, 5), // Mar 1, 2005
    ('1111WHITE', 2876333, 3, 2.6, 1121, 5), // Nov 21, 2005
    ('1111WHITE', 2876333, 3, 2.7, 115, 6); // Jan 15, 2006
    I also have a table call 'item_specs' with about 8,000 entries:
    item_number - varchar(32)
    vendor_id - integer
    warehouse - integer
    price - float

    Code:
    INSERT INTO item_specs VALUES
    ('5555BLUE', 3345812, 15, 1.1),
    ('1111WHITE', 2876333, 3, 2.7);
    'item_specs' holds the current status for an item. What I want is to somehow get the latest purchase from the 'purchases' table for each item in 'item_specs', and then I will create a report showing entries from 'item_specs' that have a different price from the last purchase.

    Code:
    SELECT *
    FROM item_specs LEFT JOIN purchases
    ON item_specs.item_number = purchases.item_number AND item_specs.vendor_id = purchases.vendor_id AND item_specs.warehouse = purchases.warehouse
    GROUP BY item_specs.item_number, item_specs.vendor_id, item_specs.warehouse
    ORDER BY purchases.year DESC, purchases.month DESC
    I know the above query won't work, but it's a start. Is there a way to do this without too many queries? I want to match every 'item_spec' entry with its last purchase.

    Thanks in advance! I've been trying to figure this out for days.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    First off, the code tages are used for preserving the formatting of your SQL statement, in the future please format to make it easier to read. Next off, why is everything duplicated between the tables and who decided on that kind of a format for a date? A better design, would be something like:
    Item_specs table:
    Code:
    item_number - varchar(32)
    item_seq_no - smallint
    vendor_id - integer
    price - float
    eff_beg - date
    eff_end - date
    Purchases table:
    Code:
    item_number - varchar(32)
    item_seq_no - smallint   -- not necessary, as you could check that purchase
                                        date is between the date ranges in item_specs
    warehouse - integer
    purchase_dt - date
    Regardless of all the inherent problems you have on your hands, something like the following should get you moving in the right direction.
    Code:
    SELECT never,use,*,explicitly,name,the,columns,you,want
        FROM item_specs is
    LEFT JOIN table(select purchases.item_number
                         , purchases.vendor_id
                         , purchases.warehouse
                         , purchases.price
                         , MAX(purchases.year || purchases.month) AS MON_YR
                        from purchases
                    group by purchases.item_number
                           , purchases.vendor_id
                           , purchases.warehouse
                           , purchases.price) AS PURCHAS
    
    
       ON is.item_number = purchas.item_number
      AND is.vendor_id    = purchas.vendor_id
      AND is.warehouse   = purchas.warehouse
      AND is.price         <> purchas.price
    ORDER BY purchases.year DESC, purchases.month DESC
    Dave

  3. #3
    Join Date
    Aug 2010
    Posts
    2
    Thanks a lot for the help. After a little tweaking, the query works perfectly. I had to order it all by item_number, vendor_id, warehouse, MON_YR DESC, and then I took the first one from each item_number, vendor_id, warehouse group and ignored the rest. I had set MON_YR to MAX(RIGHT('00'||PLRDY,2) || RIGHT('0000'||PLRDM,4)) so that the sorting would work correctly.

    Quote Originally Posted by dav1mo View Post
    Next off, why is everything duplicated between the tables
    It's not really duplicated... one table is a purchase history, and the other contains the current prices for the items. Just because the last purchase was for one price, it doesn't necessarily mean that's the "official" new price for the item. If I could change things, I would.

    Quote Originally Posted by dav1mo View Post
    and who decided on that kind of a format for a date?
    I don't know, but it's a pain to work with, and I cannot change anything structure-wise.

Posting Permissions

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