Query_Quiz_3

SELECT

    # Название покупателя
    (SELECT of_name FROM contragents WHERE id = last_max.BUYER)
      AS Buyer,

    # Номер накладной
    c.number AS Num,

    # Сумма всех позиций с НДС
    (SELECT SUM(price_with_nds) FROM consignment_goods
      WHERE consignments_id = last_max.C_ID) AS PRICE_SUM,

    # Сумма строк по накладной
    (SELECT COUNT(*) FROM consignment_goods
      WHERE consignments_id = last_max.C_ID) AS GC_COUNT

FROM
(
  # здесь берется максимальная сумма не по всем записям накладной, а
  # максимум по всем записям каждой накладной
  SELECT
    c.id as C_ID,
    cg.id as CG_ID,
    c.buyer_id AS BUYER,

    # Сумма с НДС по всем записям каждой накладной - для оценки максимума
    SUM(cg.price_with_nds)

  FROM `consignment_goods` AS cg LEFT JOIN `consignments` AS c
    ON cg.consignments_id = c.id

  GROUP BY c.id,
    YEAR(FROM_UNIXTIME(c.date)),
    MONTH(FROM_UNIXTIME(c.date)),
    DAY(FROM_UNIXTIME(c.date))

  ORDER BY c.date DESC

  LIMIT 1
)
  AS last_max,

`consignment_goods` AS cg,
`consignments` AS c

WHERE cg.id = last_max.CG_ID
   AND c.id = last_max.C_ID