Trouble using MAX() with a table JOIN

Question

Here is the current query i am trying to convert, it takes ONE item id and then receives the corresponding price/qty/vendor_id with the most recent date and a type that is not equal to TRAN.

 SELECT  cs_po_products.price, cs_po_products.qty, cs_po.vendor_id 
        FROM cs_po_products, cs_po
        WHERE cs_po_products.stock_number = :items_id
        AND cs_po_products.po_number = cs_po.id 
        AND type !='TRAN'
        ORDER BY cs_po.date_ordered DESC
        LIMIT 1

This query works fine, however it has to be done quite a few time which makes loading the page a bit slow. I am trying to create a query that will get the ROW of the Newest date_ordered for EVERY stock_number. While searching for an answer i have come across quite a few techniques but the most common being a JOIN of the two tables with a MAX() of date_ordered in the join. But alas i cannot seem to come up with the correct query.

This is one of many attempts but the one i think is the closest.

SELECT a.price, a.qty, b.vendor_id, a.stock_number, b.max, b.type
 FROM cs_po_products a
 LEFT JOIN (SELECT MAX(date_ordered)as max,vendor_id,type, date_ordered,id FROM cs_po) b 
 ON b.id = a.po_number 
 WHERE stock_number != '' AND date_ordered IS NOT NULL 
 AND type !='TRAN'
 ORDER BY stock_number, date_ordered DESC

If anyone could point me in the right direction, thanks in advance

EDIT:

all of the answers provided still give more than one row for a stock number, for example:

 price      qty     vendor_id   stock_number    date_ordered
 0.05446    123750  51          00010005.01S    2014-02-24 15:29:00
 0.05446    123750  51          00010005.01S    2014-01-02 14:25:00
 0.05446    123750  51          00010005.01S    2013-11-04 13:48:00
 0.05402    123750  51          00010005.01S    2013-08-20 10:02:00
 0.0532     123750  51          00010005.01S    2013-07-12 09:21:00
 0.0538     123750  51          00010005.01S    2013-04-02 12:15:00
 0.0532     123750  51          00010005.01S    2012-12-27 00:00:00
 0.0555     101750  51          00010005.01S    2012-11-07 10:55:00
 0.555      137500  51          00010005.01S    2012-11-02 09:39:00
 0.0532     137500  51          00010005.01S    2012-11-02 09:37:00
 0.0532     123750  51          00010005.01S    2012-10-03 00:00:00

the ONLY row i want in this result is the top one because it has the most recent date, but in the actual results there is many more stock_numbers below which i want those corresponding max dates aswell

Answer 1

In first query just remove limit and where condition and add group by cs_po_products.stock_number with same order by so that it will select only first occurrence. I hope that should work.

may these links can help you
SQL Select only rows with Max Value on a Column or
How to select single row based on the max value in multiple rows

Answer 2

Try this way:

 SELECT a.price, a.qty, b.vendor_id, a.stock_number, b.max, b.type
   FROM cs_po_products a
         LEFT JOIN (SELECT vendor_id, type, date_ordered, id, 
                           MAX(date_ordered)as max 
                      FROM cs_po
                     GROUP BY date_ordered) b 
             ON a.po_number = b.id
  WHERE a.stock_number != '' 
    AND b.date_ordered IS NOT NULL 
    AND b.type !='TRAN'
  ORDER BY a.stock_number, b.date_ordered DESC

Answer 3

A sub query to get the latest date_ordered for each id from cs_po, then joining that against cs_po to get the other values from the table:-

SELECT  cs_po_products.price, cs_po_products.qty, cs_po.vendor_id 
FROM cs_po_products
INNER JOIN 
(
    SELECT id, MAX(date_ordered) AS date_ordered
    FROM cs_po
    GROUP BY id
) sub0
ON cs_po_products.po_number = sub0.id 
INNER JOIN cs_po
ON sub0.id  = cs_po.id 
AND sub0.date_ordered = cs_po.date_ordered
WHERE type !='TRAN'

EDIT.

Best guess at what you are trying to get. But without table layouts and input test data this is very much a guess.

SELECT a.*, b.*
FROM
(
    SELECT a.stock_number, MAX(date_ordered) AS date_ordered
    FROM cs_po_products a
    INNER JOIN cs_po b 
    ON b.id = a.po_number
    GROUP BY a.stock_number
) sub0
INNER JOIN cs_po_products a
ON a.stock_number = sub0.stock_number
INNER JOIN cs_po b 
ON b.id = a.po_number
AND b.date_ordered = sub0.date_ordered
WHERE type !='TRAN'

Answer 4

Avoid sub-queries with the anti-join:

SELECT cpp.price, cpp.qty, cp1.vendor_id, cpp.stock_number, cp1.date_ordered 
FROM cs_po_products cpp
JOIN cs_po cp1
  ON cp1.id = cpp.po_number
  AND cp1.date_ordered IS NOT NULL
  AND cp1.type != 'TRAN'
LEFT JOIN cs_po cp2
  ON cp2.id = cpp.po_number
  AND cp2.type != 'TRAN'
  AND cp2.date_ordered > cp1.date_ordered
WHERE cpp.stock_number != ''
  AND cp2.id IS NULL
ORDER BY cpp.stock_number ASC

This will return multiple rows for a cs_po_products record that has more than one co_po record with the same most recent date, since you didn't specify which record to use in that case.

Some of your columns were ambiguous about which table they belonged to, so let me know if I got one wrong.