August 25, 2005 at 2:42 pm
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.
August 25, 2005 at 2:51 pm
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
August 25, 2005 at 3:02 pm
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
August 25, 2005 at 3:04 pm
Not set based... what are you gonna do if there's 1 M names in there??
August 25, 2005 at 3:04 pm
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
Vasc
August 25, 2005 at 3:05 pm
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 !!!**
August 25, 2005 at 3:06 pm
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 !!!**
August 25, 2005 at 3:08 pm
First is mine ...sec is just a translation of what Remi suggested : ) ( which is like yours)
the Exec plan for the first is cheaper
Vasc
August 25, 2005 at 3:13 pm
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