I have a doubt in logic of SQL query please help me in resolving this.

How to select a row with a earliest posting date for a single invoice number in SQL Server:

Existing table:

I want to query the output like this:

I am struggling with logic for how to compare dates of two posting date and return the row with lowest posting date column value for each invoice number in SQL Server.

I tried to use MIN() and INNER JOIN before but it didn't help in my complex codebase so I am displaying it in simple manner here.

EDIT: updated from the previous one I want this change to occur only to newly created invoices not already existing ones, so i want to be applied if the invoice date is greater than the a particular date or other way occur only if the invoice number is already existing in database AND also having two posting dates for the same invoice number.

The query should produce a table if the invoice date is not existing in sample database and also having two posting dates then it should display a single row with earliest posting date (having same invoice number) eg :china

if the invoice date is existing in a sample database and having two posting dates then it should display all the rows with same invoice number with no change eg: london, australia

Other rows which have single posting date ( whether they exist or not existing in a sample database) they should display their row. eg:India

Existing Example table:

I want to query as single Query to display output like this:

I'm not able to that as a single SELECT query and I also checked with particular date instead of checking in database, it too failed. Please help me on this.

Remark : this is sample table, it will be filled with dynamic values with dynamic columns from codebase, so, ommiting American row is my not expected output, getting output based on the logic is the expected output.

Thank you

CodePudding user response：

The window functions are your friends. Well worth your time to get comfortable with them. Also not clear if you need NAME in the partition by

Also, if you want to see ties ... use dense_rank() instead of row_number()

More Performant

Select *
 From  (
         Select *
               ,RN = row_number() over (partition by InvoiceNumber,Name order by PostingDate)
           From YourTable
       ) A
 Where RN=1

Another Option

Select top 1 with ties *
 From  YourTable 
 Order by row_number() over (partition by InvoiceNumber,Name order by PostingDate)