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.

[sculptorlv]

Still got one strange trouble.

My code now is:

LOAD

  ID,

   FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

          Date(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

AND

TABLE_Document_Rows.Status2 = 4

GROUP BY

TABLE_Document_Rows."No_",

TABLE_Document_Rows."FA Location SubCode",

TABLE_Document_Rows.Status2;

Looks like it works ... but for some minor ID - it DOESN'T . For exmaple for ID values EXTRAA003681 I got result:

But there are records in real base:

Added later:

I have duplicated records with the same date, but function FirstStoredValue get error in this case. How can I chose only last record in this case?

[sujith1011]

get every record From SQL, like this

SELECT

  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

AND

TABLE_Document_Rows.Status2 = 4

[sculptorlv]

Hello!

I don't need every record. This is my problem, I need only ONE record with the last date. But in case, when there are two records with the same date, I need to choose the ONE with largest value from additional column Entry.nr.