Member Avatar for RaghdaAbdalla

Hello .....
I writing sql statment on access 2007, all what I need to do is to get the max value of a column depending on the max of another column..

an exmaple of the data is as below:
Invoice No-------- Document no--------Document Date-----Value
1001115600188757 /// 5100002019//////13/12/2010////////-30469.50
1001115600188757 /// 5100002018//////13/12/2010////////30469.50
1001115600188757 /// 5100001968///// 13/12/2010//////// -30469.50

I need the sql to get Distinct invoice no with the latest document date and the biggest document no summing all the values

to get by the end:
Invoice No---------------Document no------Document Date-----Value
1001115600188757////////5100002019////////13/12/2010////////30469.50

if the example was:
Invoice No-------- Document no--------Document Date-----Value
1001115600188757 /// 5100002019//////13/12/2009////////-30469.50
1001115600188757 /// 5100002018//////13/12/2010////////30469.50
1001115600188757 /// 5100001968///// 13/12/2010//////// -30469.50


the answer to be:
1001115600188757 /// 5100002018//////13/12/2010////////30469.50


could any one help me in writing the sql statment for this????

Edited by RaghdaAbdalla because: n/a
  • 2 Contributors
  • 5 Replies
  • 122 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

The goal is one row per invoice that shows (a) the total of all value rows for that invoice, and (b) the document info chosen by "latest document_date, and if there are ties take the highest document_no". A plain GROUP BY with MAX(document_no) (as tried) will return the largest document number overall, not the document number that belongs to the latest date. The correct pattern is: aggregate the totals per invoice, then pick the doc fields from the row that matches the invoice's max date (and highest doc_no among those).

An Access-friendly solution uses correlated TOP 1 subqueries to pick the latest row per invoice while grouping to get the sum:

SELECT I.invoice_no,
       (SELECT TOP 1 J.document_no
        FROM Invoice AS J
        WHERE J.invoice_no = I.invoice_no
        ORDER BY J.document_date DESC, J.document_no DESC) AS LatestDocumentNo,
       (SELECT TOP 1 J.document_date
        FROM Invoice AS J
        WHERE J.invoice_no = I.invoice_no
        ORDER BY J.document_date DESC, J.document_no DESC) AS LatestDocumentDate,
       SUM(I.value) AS TotalValue
FROM Invoice AS I
GROUP BY I.invoice_no;

On SQL Server a clearer, more efficient approach uses window functions:

WITH cte AS (
  SELECT invoice_no,
         document_no,
         document_date,
         SUM(value) OVER (PARTITION BY invoice_no) AS TotalValue,
         ROW_NUMBER() OVER (PARTITION BY invoice_no ORDER BY document_date DESC, document_no DESC) AS rn
  FROM invoice
)
SELECT invoice_no, document_no AS LatestDocumentNo, document_date AS LatestDocumentDate, TotalValue
FROM cte
WHERE rn = 1;

Notes and troubleshooting:

  • Ensure document_date is a real date type; text dates will sort incorrectly.
  • If document_no is stored as text, convert it to numeric for correct ordering (or pad it consistently).
  • Correlated subqueries in Access can be slow on large tables—index invoice_no and (invoice_no, document_date) where possible.
  • If multiple rows share identical date and document_no, add additional tie-breaker columns to ORDER BY.

Recommended Answers

All 5 Replies

Member Avatar for urtrivedi 
select * from invoice 
where document_no in (
                 select max(document_no) from invoice 
                 where document_date in (select max(document_date) from invoice)
                     )
Member Avatar for RaghdaAbdalla

Thanks Urtrivedi for your reply but the point is that If I made select * I wont be able to make sum on the values
It seems I wrote NO other than And in by explanation ....sorry for that
an example to better explain my point:
Invoice No-------- Document no--------Document Date-----Value
1001115600188757 /// 5100002019//////13/12/2009////////10
1001115600188757 /// 5100002018//////13/12/2010////////20
1001115600188757 /// 5100001968///// 13/12/2010//////// 50


the answer to be:
1001115600188757 /// 5100002018//////13/12/2010////////80

Member Avatar for urtrivedi 
select invoice_no, max(doucment_no), max(document_date), sum(value) 
from invoice
group by invoice_no
Member Avatar for RaghdaAbdalla 
select invoice_no, max(doucment_no), max(document_date), sum(value) 
from invoice
group by invoice_no

this will get the Max number in all the rows not the max number for the max date :)

Member Avatar for urtrivedi

I hope this would work.

select a.invoice_no, a.max_date,a.total, b.max_document_no 
from (
       SELECT invoice_no, max(document_date) max_date, sum(value) total
       FROM invoice 
       GROUP BY invoice_no
      ) a
inner join 
     (
        SELECT invoice_no,document_date max(document_no) max_document_no
        FROM invoice 
        GROUP BY invoice_no,document_date
     ) b on a.invoice_no=b.invoice_no and a.document_date=b.document_date
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.