Group - get rowid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group - get rowid

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2008-09-23 : 05:36:25

I have read forumid:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110589

How can I get RowId (from max value in group)?

create table #tablec
(RowID INT, AcctNbr int, Dateadded datetime, RenewalDate datetime, lastgiftdate datetime)

insert into #tablec
values (1,112, '1/2/2007 11:59:59.99 PM', '6/4/2007 11:59:59.99 PM', '11/3/2007 11:59:59.99 PM')
insert into #tablec
values (2,112, '1/2/2007 11:59:59.99 PM', '6/4/2007 11:59:59.99 PM', '12/3/2007 11:59:59.99 PM')
insert into #tablec
values (3,112, '1/2/2007 11:59:59.99 PM', '6/4/2007 11:59:59.99 PM', '11/3/2007 11:59:59.99 PM')
insert into #tablec
values (4,122, '3/2/2007 11:59:59.99 PM', '8/4/2007 11:59:59.99 PM', '1/3/2007 11:59:59.99 PM')
insert into #tablec
values (5,122, '3/2/2007 11:59:59.99 PM', '8/4/2007 11:59:59.99 PM', '2/3/2007 11:59:59.99 PM')
insert into #tablec
values (6,122, '3/2/2007 11:59:59.99 PM', '8/4/2007 11:59:59.99 PM', '4/3/2007 11:59:59.99 PM')
insert into #tablec
values (7,1442, '4/2/2007 11:59:59.99 PM', '9/4/2007 11:59:59.99 PM', '1/3/2007 11:59:59.99 PM')
insert into #tablec
values (8,1442, '4/2/2007 11:59:59.99 PM', '9/4/2007 11:59:59.99 PM', '2/3/2007 11:59:59.99 PM')
insert into #tablec
values (9,1442, '4/2/2007 11:59:59.99 PM', '9/4/2007 11:59:59.99 PM', '2/13/2007 11:59:59.99 PM')
insert into #tablec
values (10,11266, '5/2/2007 11:59:59.99 PM', '10/4/2007 11:59:59.99 PM', '2/3/2007 11:59:59.99 PM')
insert into #tablec
values (11,11266, '5/2/2007 11:59:59.99 PM', '10/4/2007 11:59:59.99 PM', '2/5/2007 11:59:59.99 PM')

select * from #tablec order by 1, 4 desc

RowID in select is not part of group / RowId can not contained in group

select a.AcctNbr, a.Dateadded, a.RenewalDate, max(a.lastgiftdate) as [LastGiftDate] from #tablec a
group by a.AcctNbr, a.Dateadded, a.RenewalDate

DROP TABLE #tablec

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 05:46:42

you mean maximun avalue of rowid for each group? just use MAX(RowID) in select list

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-09-23 : 05:49:46

SELECT TOP 1
MAX(RowID),
AcctNbr,
Dateadded,
RenewalDate,
lastgiftdate
FROM
#tablec

**************************
Check Check and double check it's the safest way

Clas
Starting Member

33 Posts

Posted - 2008-09-23 : 06:03:55

select a.AcctNbr, max(a.lastgiftdate) as [LastGiftDate] from #tablec a
group by a.AcctNbr

Result:
AcctNbr --- LastGiftDate (max)

112 --- 2007-12-03
122 --- 2007-04-03
1442 --- 2007-02-13
11266 --- 2007-02-05

How can I also get RowID:

RowID --- AcctNbr --- LastGiftDate (max)

2 --- 112 --- 2007-12-03
6 --- 122 --- 2007-04-03
9 --- 1442 --- 2007-02-13
11 --- 11266 --- 2007-02-05

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 06:09:17

quote:
Originally posted by Clas

select a.AcctNbr, max(a.lastgiftdate) as [LastGiftDate] from #tablec a
group by a.AcctNbr

Result:
AcctNbr --- LastGiftDate (max)

112 --- 2007-12-03
122 --- 2007-04-03
1442 --- 2007-02-13
11266 --- 2007-02-05

How can I also get RowID:

RowID --- AcctNbr --- LastGiftDate (max)

2 --- 112 --- 2007-12-03
6 --- 122 --- 2007-04-03
9 --- 1442 --- 2007-02-13
11 --- 11266 --- 2007-02-05

SELECT t.RowID,t.AcctNbr,t.LastGiftDate
FROM #tablec t
INNER JOIN
(select a.AcctNbr, max(a.lastgiftdate) as [LastGiftDate] from #tablec a
group by a.AcctNbr
)tmp
ON tmp.AcctNbr=t.AcctNbr
AND tmp.LastGiftDate=t.lastgiftdate

Clas
Starting Member

33 Posts

Posted - 2008-09-23 : 06:20:25

Thanks for your help !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources