Group by with order by?

the · #1 (**permalink**) 08-12-10, 10:29

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.

dav1mo · #2 (**permalink**) 08-12-10, 14:12

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

the · #3 (**permalink**) 08-12-10, 17:25

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

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

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.