Need help with removing duplicates from a table

[#1]

Hi All

I have a table with 2 columns.  GUID, Date

GUID, Date

1, 1/30/2012

2, 1/01/2012

3, 1/1/2012

1, 1/31/2012

5, 1/5/2012

What's the best to remove the duplicate GUID based on the latest Date?

From the table above,

I want to get this:

2, 1/01/2012

3, 1/1/2012

1, 1/31/2012

5, 1/5/2012

I have tried using the peek function and doing a load ordered by guid desc, date desc, but it still leaves some duplicates and I don't undertand why.

Is there a better way to do this?

[#2]

Thank you.

I was confused by the error highlight in the editor and also in the reference manual, "The word distinct before expression or a field is allowed but has no meaning.

[swuehl]

Yes, editor errors are annoying. And the Help also could be updated at some places.

But in my QV11 version, it says:

...If the word distinct occurs before the expression, all duplicates will be disregarded.

So here it seems quite helpful.

Regards,

Stefan

[#4]

I just discovered something interesting.  Please see attached file for reference.

When I tried to do a order by in the same table as I am doing the previous, it breaks.  I had to create a new sort table, sort the data first, and then de-dupe the data.

new_table2 shows that it doesn't work while new_table4 does work after I do a sort_table first.

Why is that?

Also, since I have to do 2 table loads using the previous function, which will be faster?  Using distinct or use the 2 tables?