All Forums Analytics
geom 1 post Joined 02/11
20 Feb 2012
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.

 

 

 

ulrich 816 posts Joined 09/09
20 Feb 2012

select *

from table

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

should work

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.