I agree, when it comes to endings, you can't look past The Usual Suspects. I can't really say why it's brilliant without spoiling it but the movie itself is worth watching just to see how the end plays out.
Y Tu Mamá También also has quite a surprising but sad ending that's worth noting.