Only last date record

[sculptorlv]

Hello!

I have such SQL script:

SELECT

  MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

  TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

  TABLE_Document_Rows."No_" AS ID

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

WHERE

TABLE_Document_Rows."FA Quantity" = 1

GROUP BY

TABLE_Document_Rows."No_",

TABLE_Document_Rows."FA Location SubCode";

And the result is:

I used MAX function to delete all duplicated rows with same ID. But it doesn't work. I need only one ID row with the lates date.

How can I do it? I hope for your help.

[sunny_talwar]

May be add a preceding load like this:

LOAD ID,

          FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

          Max(Post_Date) as Post_Date

Group By ID;

SELECT

  MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

  TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

  TABLE_Document_Rows."No_" AS ID

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

WHERE

TABLE_Document_Rows."FA Quantity" = 1

GROUP BY

TABLE_Document_Rows."No_",

TABLE_Document_Rows."FA Location SubCode";

[sculptorlv]

Uhh.. worst case. I works, but I don't understand till ens - how it works...

Thank you anyway!! It will help me!

Don't work. I got strange results - ID and Date are not correctly connected.

[sunny_talwar]

What did not work? Can you share the output of what you got when you ran the above code?

[sculptorlv]

Sure!

The result:

And the real Date Base:

The latest date for STAND001604 in real base is 03.09.2015

[sunny_talwar]

Seems like it may be an issue with your date interpretation:

Can you check if you have this on the main page

SET DateFormat='DD.MM.YY';

and slight change in the script:

SET DateFormat='DD.MM.YY';

LOAD ID,

          FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

          Date(Max(Post_Date)) as Post_Date

Group By ID;

SELECT

  MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

  TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

  TABLE_Document_Rows."No_" AS ID

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

WHERE

TABLE_Document_Rows."FA Quantity" = 1

GROUP BY

TABLE_Document_Rows."No_",

TABLE_Document_Rows."FA Location SubCode";

[sculptorlv]

Yes, I have this format and I also did Date(Max...) before.

[sujith1011 · Accepted solution]

Small tweaking to sunny's solution

Please try this

LOAD ID,

          FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

          FirstSortedValue(Post_Date, -Post_Date) as Post_Date

Group By ID;

SELECT

  MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

  TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

  TABLE_Document_Rows."No_" AS ID

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

WHERE

TABLE_Document_Rows."FA Quantity" = 1

GROUP BY

TABLE_Document_Rows."No_",

TABLE_Document_Rows."FA Location SubCode";

[sujith1011]

Looking at the data seems like the period in date format could be causing the issue

LOAD ID,

          FirstSortedValue(Doc_Location_Subcode, -date(REPLACE(Post_Date,'.','/'),'DD/MM/YYYY')) as Doc_Location_Subcode,

MAX(date(REPLACE(Post_Date,'.','/'),'DD/MM/YYYY')) as Post_Date

RESIDENT TRNDATE

Group By ID;

[sculptorlv]

Thank YOU VERY much!!

This works!