How to select a full record when using MAX () with GROUP BY

Question

Using MYSQL, I would like to reorganize the following statement SELECTto return an entire record containing the latestinvoice_date :

> SELECT id, invoice, invoice_date
  FROM invoice_items
  WHERE lot = 1047

id    invoice_id   invoice_date
-----------------------------------
3235    1047         2009-12-15 11:40:00
3295    1047         2009-12-15 16:00:00
3311    1047         2009-12-15 09:30:00
3340    1047         2009-12-15 13:50:00

Using the MAX () aggregation function and the GROUP BY clause gives me part of the path:

> SELECT id, invoice_id, max(invoice_date)
  FROM invoice_items
  WHERE invoice_id = 1047
  GROUP BY invoice_id


id    invoice_id   invoice_date
-----------------------------------
3235    1047         2009-12-15 16:00:00

Please note that the query looks correct if id, but the returned id(3235) is not idfor the record containing MAX(invoice_date)(3295), this is the idfirst record in the original query.

How do I reorganize this request to give me a complete record containing MAX(invoice_date)?

The solution is to use the GROUP BY clause because I need to get the latest one invoice_datefor each invoice.

Answer 1

" n--".

MySQL:

SELECT i1.*
FROM invoice_items i1
LEFT OUTER JOIN invoice_items i2
  ON (i1.invoice_id = i2.invoice_id AND i1.invoice_date < i2.invoice_date)
WHERE i2.invoice_id IS NULL;

: i1 i2 invoice_id . (.. i2 - - ), i1 invoice_id.

MySQL, GROUP BY, .

Answer 2

, - invoice_items, , , - :

SELECT * FROM invoice_items 
WHERE invoice_date IN (SELECT MAX(invoice_date) FROM invoice_items)

, :

SELECT * FROM invoice_items
ORDER BY invoice_date DESC
LIMIT 1

Answer 3

,

" - "

Select * From invoice_items
Where invoice_date =
   (Select Max(invoice_date)
    From invoice_items)

- , . Invoice_Id, , . , -? " , -, - ( ) ..

Answer 4

Here is my attempt:

SELECT t1.*
FROM INVOICE_ITEMS t1,
   (SELECT INVOICE_ID, MAX(INVOICE_DATE) as invoice_date2
      FROM INVOICE_ITEMS
     GROUP BY INVOICE_ID) t2
WHERE t1.invoice_id = t2.invoice_id
AND t1.invoice_date = t2.invoice_date2

Answer 5

SELECT * 
FROM invoice_items
WHERE lot = 1047
ORDER BY invoice_date desc LIMIT 1

or better if your identifier is your primary key and is always growing

SELECT * 
FROM invoice_items
WHERE lot = 1047
ORDER BY id desc LIMIT 1