Query Help

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  October 17, 2012 at 3:17 pm

  #393411

  I am trying to come up a better way to query this data

  ;with TempCTE(State,County,City,Action,Timestamp) AS(

  select 'Texas','Harris','Houston','Census Taken','11/12/2010 00:00:00' Union all

  select 'Texas','Harris','Houston','Census Submitted','12/15/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Planned','9/10/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Taken','11/11/2010 00:00:00'

  ) select distinct State,County,City from TempCTE;

  here is my expected result

  Texas HarrisHoustonCensus Submitted12/15/2010 00:00:00

  OklahomaJohnsonTulsaCensus Taken11/11/2010 00:00:00

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  October 17, 2012 at 3:49 pm

  #1550243

  Oh well here is one of the solution. If you have a faster solution, I would love to know it

  Thanks!

  declare @Temp table(state varchar(50),county varchar(50), city varchar(50),Action varchar(50), ActDateTime DateTime)

  insert into @Temp(State,County,City,Action, ActDateTime)

  select 'Texas','Harris','Houston','Census Taken','11/12/2010 00:00:00' Union all

  select 'Texas','Harris','Houston','Census Submitted','12/15/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Planned','9/10/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census inprogress','11/11/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Taken','11/19/2010 00:00:00'

  select T1.State, T1.County,T1.City,T1.Action,T1.ActDateTime

  from @Temp T1

  inner join (select State,County,City, max(ActDateTime) as ActDateTime

  from @Temp

  group by State,County,City

  ) T2

  ON T1.State=T2.State

  and T1.County=T2.County

  and T1.City=T2.City

  and T1.ActDateTime=T2.ActDateTime

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 17, 2012 at 4:18 pm

  #1550247

  Please test the solution, I might have made a mistake.

  declare @Temp table(state varchar(50),county varchar(50), city varchar(50),Action varchar(50), ActDateTime DateTime)

  insert into @Temp(State,County,City,Action, ActDateTime)

  select 'Texas','Harris','Houston','Census Taken','11/12/2010 00:00:00' Union all

  select 'Texas','Harris','Houston','Census Submitted','12/15/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Planned','9/10/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census inprogress','11/11/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Taken','11/19/2010 00:00:00';

  WITH CTE AS(

  SELECT State, County, City, Action, ActDateTime,

  ROW_NUMBER() OVER( PARTITION BY State, County, City ORDER BY ActDateTime DESC) AS rn

  FROM @Temp )

  SELECT State, County, City, Action, ActDateTime

  FROM CTE

  WHERE rn = 1

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  October 17, 2012 at 5:23 pm

  #1550255

  Luis Cazares (10/17/2012)

  ---

  Please test the solution, I might have made a mistake.

  declare @Temp table(state varchar(50),county varchar(50), city varchar(50),Action varchar(50), ActDateTime DateTime)

  insert into @Temp(State,County,City,Action, ActDateTime)

  select 'Texas','Harris','Houston','Census Taken','11/12/2010 00:00:00' Union all

  select 'Texas','Harris','Houston','Census Submitted','12/15/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Planned','9/10/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census inprogress','11/11/2010 00:00:00' Union all

  select 'Oklahoma','Johnson','Tulsa','Census Taken','11/19/2010 00:00:00';

  WITH CTE AS(

  SELECT State, County, City, Action, ActDateTime,

  ROW_NUMBER() OVER( PARTITION BY State, County, City ORDER BY ActDateTime DESC) AS rn

  FROM @Temp )

  SELECT State, County, City, Action, ActDateTime

  FROM CTE

  WHERE rn = 1

  Luis

  This works too, It is in facts cleaner implementation, I will see which of those two solutions runs faster as I have to deal with millions of records.

  Thanks for quick response.