top 2 dates

  • 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.

  • 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

  • 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

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

  • 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

  • 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 !!!**

  • 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 !!!**

  • 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

  • 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

Viewing 9 posts - 1 through 8 (of 8 total)

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