how to get top 1 record from each group

Question

I have a table of invoices with a unique id, invoice date and company id. The problem I'm having is that the invoice dates are only the date part and don't include time. We have some companies that have multiple invoices on the same date, so the following query returns all records for that date instead of the intended 1 latest invoice. Is it possible to also get the max id of the row to limit it to just one result per date/company? If so, how?

SELECT  accountinginvoice.CompanyId, accountinginvoice.invoicedate
    FROM accountinginvoice
    INNER JOIN (
        SELECT MAX(invoicedate) as invoicedate, companyid FROM accountinginvoice
        GROUP BY companyid
    ) AS ai ON (accountinginvoice.invoicedate = ai.invoicedate
        AND accountinginvoice.companyid = ai.companyid)
        
        order by  accountinginvoice.invoicedate

Here's an example of the results I'm getting.

id   | invoicedate             | companyid
1037 | 2021-01-06 00:00:00.000 | 6639
1039 | 2021-01-06 00:00:00.000 | 6639
1040 | 2021-01-06 00:00:00.000 | 6639
1045 | 2021-01-06 00:00:00.000 | 6639

Do you want the first (or latest) invoice in the group, or a row that represents the total amount for all the invoices in a day? — Ann L., Aug 31 '21 at 13:07
I just updated the question to show the results I'm getting. Ideally, I'd like to get the row with the id 1045. — geoff swartz, Aug 31 '21 at 13:09

score 0 · Accepted Answer · answered Aug 31 '21 at 13:14

Okay, this is from memory:

;
WITH cte AS (
   SELECT  *
        ,  row_index = row_number() OVER (PARTITION BY companyId, invoiceDate
                                          ORDER BY invoiceID desc )
   FROM    accountingInvoice
)
SELECT     cte.invoiceId,
           cte.CompanyId, 
           cte.invoicedate
FROM       cte
WHERE      cte.row_index = 1

This should give you the last invoice per date per company. If you want a particular date and company, add them as a WHERE clause to the final query.

Larnu's link is also good, although I did this from memory.

how to get top 1 record from each group

1 Answers1