top 2 dates

• sahana

  SSCrazy

  Points: 2530

  More actions

  August 25, 2005 at 2:42 pm

  #102742

  I have a table where I have two columns name and date.

  Sample data is hown below.

  Name     date

  aaa 8/19/2005

  bbb 8/19/2005

  ccc  8/20/2005

  aaa 7/29/2005

  bbb 8/24/2005

  bbb 8/2/2005

  ccc 8/24/2005

  aaa 8/11/2005

  bbb 8/8/2005

  aaa 8/8/2005

  bbb  8/25/2005

  ccc 7/28/2005

  I want a query to return email and top2(latest) dates,like the following

  aaa  8/19/2005

  aaa 8/11/2005

  bbb 8/25/2005

  bbb 8/24/2005

  ccc 8/24/2005

  ccc 8/20/2005

  Can anyone help me on this issue.

  Thanks.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 25, 2005 at 2:51 pm

  #585183

  Here's an exemple :

  SELECT O.XType

  , O.name

  FROM dbo.SysObjects O

  WHERE ID IN (SELECT TOP 90 PERCENT ID FROM dbo.SysObjects O2 WHERE O2.XType = O.XType order by O2.Name)

  ORDER BY O.XType, O.Name

• mimorr

  SSCarpal Tunnel

  Points: 4850

  More actions

  August 25, 2005 at 3:02 pm

  #585193

  Another option?

  create table test

  (col001 char(3),

  col002 datetime

  )

  go

  insert into test

  select 'aaa', '8/19/2005'

  union

  select 'bbb','8/19/2005'

  union

  select 'ccc','8/20/2005'

  union

  select 'aaa','7/29/2005'

  union

  select 'bbb','8/24/2005'

  union

  select 'bbb','8/2/2005'

  union

  select 'ccc','8/24/2005'

  union

  select 'aaa','8/11/2005'

  union

  select 'bbb','8/8/2005'

  union

  select 'aaa','8/8/2005'

  union

  select 'bbb','8/25/2005'

  union

  select 'ccc','7/28/2005'

  go

  create table #test

  (col001 char(3),

  col002 datetime)

  go

  insert into #test

  select top 2 * from test

  where col001 = 'aaa'

  order by col002 desc

  go

  insert into #test

  select top 2 * from test

  where col001 = 'bbb'

  order by col002 desc

  go

  insert into #test

  select top 2 * from test

  where col001 = 'ccc'

  order by col002 desc

  go

  select * from #test

   

  
  
  Michelle

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 25, 2005 at 3:04 pm

  #585196

  Not set based... what are you gonna do if there's 1 M names in there??

• Vasc

  SSCarpal Tunnel

  Points: 4623

  More actions

  August 25, 2005 at 3:04 pm

  #585195

  declare @t table([name] varchar(3),[date] datetime)

  insert into @t

  select 'aaa', '8/19/2005' union all

  select 'bbb', '8/19/2005' union all

  select 'ccc', '8/20/2005' union all

  select 'aaa', '7/29/2005' union all

  select 'bbb', '8/24/2005' union all

  select 'bbb', '8/2/2005'union all

  select 'ccc', '8/24/2005' union all

  select 'aaa', '8/11/2005' union all

  select 'bbb', '8/8/2005'union all

  select 'aaa', '8/8/2005' union all

  select 'bbb', '8/25/2005' union all

  select 'ccc', '7/28/2005'

  select a.*

  from @t a, @t b

  where a.name=b.name and a.date<=b.date

  group by a.name,a.date

  having count(*)<=2

  order by a.name,a.date desc

  --remi's

  select a.*

  from @t a

  where a.date in (select top 2 date from @t where a.name=name order by date desc)

  order by a.name,a.date desc

  ---

  Kindest Regards,

  Vasc

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  August 25, 2005 at 3:05 pm

  #585199

  just so you have enough options...

  create table t1

  (name char(3),

  dtCol smalldatetime)

  insert into t1 values('aaa', '8/19/2005')

  insert into t1 values('bbb', '8/20/2005')

  insert into t1 values('bbb', '8/19/2005')

  insert into t1 values('ccc', '8/20/2005')

  insert into t1 values('aaa', '7/29/2005')

  insert into t1 values('bbb', '8/24/2005')

  insert into t1 values('bbb', '8/2/2005')

  insert into t1 values('ccc', '8/24/2005')

  insert into t1 values('aaa', '8/11/2005')

  insert into t1 values('ccc', '7/28/2005')

  insert into t1 values('bbb', '8/25/2005')

  insert into t1 values('bbb', '8/8/2005')

  SELECT Name, DtCol

  FROM t1 A

  WHERE dtCol IN (SELECT TOP 2 dtCol FROM t1 B WHERE A.Name = B.Name

  GROUP BY Name, dtCol ORDER BY dtCol DESC)

  GROUP BY Name, dtCol

  ORDER BY Name, dtCol DESC

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  August 25, 2005 at 3:06 pm

  #585201

  oops - vasc - i think we have identical solutions - looks like i cannot type even with moderate speed anymore...

  ..take that back...looks like i have unnecessary group bys in there...next time i won't post until something has gone unanswered until midnight...

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• Vasc

  SSCarpal Tunnel

  Points: 4623

  More actions

  August 25, 2005 at 3:08 pm

  #585202

  First is mine ...sec is just a translation of what Remi suggested : ) ( which is like yours)

  the Exec plan for the first is cheaper

  ---

  Kindest Regards,

  Vasc

• mimorr

  SSCarpal Tunnel

  Points: 4850

  More actions

  August 25, 2005 at 3:13 pm

  #585206

  I'm thankful people post so I can learn something from other's questions too!  I was able to figure out how to get the exact data, but as Remi pointed out, I'd have a lot of typing to do if there were a million names.

  Thanks, I'm off to learn more about table variables and self joins.

   

  
  
  Michelle