sql - Retrieve rows with max date only when duplicate when there is more than one column in the results - Stack Overflow

admin管理员组

文章数量:1122846

Retrieve rows with max date only when duplicate when there is more than one column in the results.

In this query, Receipts (RecNum, RecDate) duplicates. I only need the most recent receipt number.

My query is :

SELECT "InvNum", "CusName", "RecNum", "RecDate" 
FROM CUS_PAY

SELECT 
    "InvNum",
    MAX("RecDate") AS "RecDate",
    MAX("CusName") AS "CusDate"
    MAX("RecNum") AS "RecNum"   
FROM
    CUS_PAY
WHERE 
    "RecNum" IS NOT NULL
GROUP BY 
    "InvNum"

Will this do it? Or what else do I need? Or do I have to use partitioning?

Retrieve rows with max date only when duplicate when there is more than one column in the results.

In this query, Receipts (RecNum, RecDate) duplicates. I only need the most recent receipt number.

My query is :

SELECT "InvNum", "CusName", "RecNum", "RecDate" 
FROM CUS_PAY

SELECT 
    "InvNum",
    MAX("RecDate") AS "RecDate",
    MAX("CusName") AS "CusDate"
    MAX("RecNum") AS "RecNum"   
FROM
    CUS_PAY
WHERE 
    "RecNum" IS NOT NULL
GROUP BY 
    "InvNum"

Will this do it? Or what else do I need? Or do I have to use partitioning?

• sql
• database
• group-by
• grouping
• partitioning

Share Improve this question Follow edited yesterday marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked yesterday DSL TechieDSL Techie 1 New contributor DSL Techie is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 3

• This question seems incomplete. Please provide the relevant schema. Also use code formatting and mention which SQL dialect you’re using. – BenderBoy Commented yesterday
• You need to share sample input and output so readers can test the logic – samhita Commented yesterday
• Tag your RDBMS and add sample data + desired results please – Dale K Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Assuming you want the record with the most recent receipt number per invoice number, you may use ROW_NUMBER() as follows:

WITH cte AS (
    SELECT p.*, ROW_NUMBER() OVER (PARTITION BY InvNum ORDER BY RecDate DESC) rn
    FROM CUS_PAY p
)

SELECT InvNum, CusName, RecNum, RecDate
FROM cte
WHERE rn = 1;

If two or more receipts could be tied for the same most recent date, and you want to return all ties, then replace ROW_NUMBER() above with RANK().

本文标签：