Your question is SQL Deduping Vendor Imports. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a query to find duplicate records in a vendor import table and keep only the most recent row.
Treat rows with the same vendor_code, invoice_number, and line_number as duplicates. If duplicate rows have the same import timestamp, retain the row with the highest import_id.
import_id, vendor_code, invoice_number, line_number, amount, imported_at, and source_file.vendor_code, invoice_number, line_number, and import_id ascending.| Column | Type | Description |
|---|---|---|
| import_idPK | INT | Unique identifier for the imported row |
| vendor_code | VARCHAR(20) | Vendor identifier from the import |
| invoice_number | VARCHAR(30) | Vendor invoice identifier |
| line_number | INT | Line number within the invoice |
| amount | DECIMAL(12,2) | Amount reported for the invoice line |
| imported_at | TIMESTAMP | Timestamp when the row entered the import table |
| source_file | VARCHAR(100) | Name of the source file that supplied the row |