get the max from table that has 2 columns unique and one duplicate

Analytics
N/A

get the max from table that has 2 columns unique and one duplicate

Ok I was sure I knew how to do this but for some reason what I was working on was not working.

I have a table with structure like this

AccountNO        TelephoneNO     Start_Date

1234567           5191111111        01/03/2011

7654321           5191111111        04/03/2011

3459012           4161112222        04/03/2011

1256789           2122221212        05/03/2011

I need it to give me only the corresponding AccountNO and TelephoneNO where the Start_date is the max for that TelephoneNO and there are multiple records like this.

I have tried and max and group by and it is not working.

Anybody got any ideas?

thanks.

1 REPLY
Supporter

Re: get the max from table that has 2 columns unique and one duplicate

select *

from table

qualify row_number() over (partition by TelephoneNO order by Start_Date desc) = 1

should work