sql help

• Ramu Poonjolai

  SSC-Addicted

  Points: 451

  More actions

  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

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  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