Need Max Record... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need Max Record...

Author

Topic

jandh98
Starting Member

10 Posts

Posted - 2010-12-22 : 09:40:05

I just can't seem to get this right - so I'd appreciate any help I can get at this point. What I'm looking for is the TypeID for the MAX ID for each EntityID.

Here's an example of my table:

ID EntityID TypeID
1000 500 1
1001 500 1
1002 500 2
1003 500 3
1004 600 2
1005 600 1
1006 700 1

The results I want are:
ID EntityID TypeID
1003 500 3
1005 600 1
1006 700 1

Hopefully that makes sense. TIA!!!!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-12-22 : 09:51:37

*second edit with updated data*


create table #yak (ID int, EntityID int, TypeID int)
 
insert into #yak (ID, EntityID, TypeID)
SELECT       1000, 500 ,1
UNION SELECT 1001 ,500, 1
UNION SELECT 1002 ,500, 2
UNION SELECT 1003 ,500, 3
UNION SELECT 1004 ,600, 2
UNION SELECT 1005 ,600, 1
UNION SELECT 1006 ,700, 1

select * from #yak

select 
	y.EntityID, y.id, y.typeid
from 
	#yak y
inner join
(select 
	max(id)as id, entityid
from 
	#yak
group by 
	EntityID
) z
on
	y.ID = z.id
and 
	y.EntityID = z.EntityID 

drop table #yak

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-23 : 03:19:53

or

select id,EntityID, typeid from
(
select ROW_NUMBER() over (partition by EntityId order by id desc) as sno,* from #yak
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-12-27 : 08:32:42

bah. I always forget row_number over()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources