0

I have two tables one is the shopping table and another is the invoice table..

now the invoice table has the primarykey column as pkinvoiceID and the same key exists in shopping table as fkinvoiceID

no the fkinvoiceID can be the same ID for multiple rows having different status like

the shopping table it can contain 3 rows with same invoiceID but different Status, i can do an inner join but i want to make sure that it picks the latest record

Jiiu
  • 1
  • 2
    Sample data and desired results is always best. For example, we don't know what Status is or if there is an effective date or identity – John Cappelletti Jan 05 '21 at 01:12
  • 1
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Jan 05 '21 at 01:36

1 Answers1

0

All you need is to use a window function like ROW_NUMBER() and PARTITION by the invoiceID ordered by your table's date field like so:

WITH CTE_Shopping_Sorted AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY invoiceID ORDER BY SomeInvoiceDateField DESC) AS SortId -- Generates a unique sequential ID within each group of invoiceIDs
    FROM Shopping
)

SELECT * -- Replace * with your actual column names for best practice
FROM CTE_Shopping_Sorted
WHERE SortId = 1

Note: If the date field you use to generate the SortId above is not distinct within each invoiceID group, then the SQL engine will randomly choose which record wins in a tie. You can add an additional field to uniqify the ORDER BY clause to determine tie-breakers.

J.D.
  • 954
  • 6
  • 22