Post reply

sql help

  • July 20, 2009 at 9:06 am

    #133977

    I have a table like this...

    mobilenumber activationdate status

    12345 01/01/2009 Pending

    12345 01/04/2009 Pending

    12345 04/06/2009 Active

    12345 04/10/2009 Active

    56789 12/01/2008 Pending

    56789 03/02/2009 Pending

    I need the output like this:

    mobilenumber activationdate status

    12345 04/10/2009 Active

    56789 03/02/2009 Pending

    Need to retrieve the latest acvitation date and the status of the mobilenumber in the table. What is the best way to do it?

    Thanks,

    Ramu

  • July 20, 2009 at 9:16 am

    #1027046

    WITH CTE AS (

    SELECT mobilenumber,activationdate,status,

    ROW_NUMBER() OVER(PARTITION BY mobilenumber ORDER BY activationdate DESC) as rn)

    SELECT mobilenumber,activationdate,statu

    FROM CTE

    WHERE rn=1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply