Search code examples
hana

Comparing ID's and pick the higher value

I need your help in HANA. I'm trying to compare dates in different rows, like:

ID~~~~~~|~~~ NAME~~~|~DATE ~~ |   STEP
--------+-----------+---------+-----------
132~~~~~|~~TEST~~~| 01.01.2001| CREATED
132~~~~~|~~TEST~~~| 05.01.2001| SOLVED
154~~~~~|~~Other~~| 06.01.2001| CREATED
175~~~~~|~~Card~~~| 08.01.2001| CREATED
175~~~~~|~~ Card~~| 09.01.2001| SOLVED

So my problem is, I want to pick the row with the highest date in HANA, but I don't know how I could do that.

First I have to compare the ID's and pick the ID's which are double or more and then pick the ID with the highest date.

I would be grateful for your help.

Best regards

Solution

  • This should be works, not tested yet

    SELECT MAX(DATE) FROM table1 GROUP BY id HAVING COUNT(*) > 1