Getting this TOP Value - 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)

Getting this TOP Value

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-08-21 : 11:38:42

intTransactionID intAliasID, Enrollment
8166 17854 3
8121 17850 4
8142 17850 4
8223 17827 2
8149 17811 5
8128 17811 5

My data looks like the above. What I need to do is grab the intTransactionID of the intAliasID, but only the greatest one for the pair. So, in the highlighted area above, what I really want to return is

intTransactionID intAliasID, Enrollment
8166 17854 3
8142 17850 4
8223 17827 2
8149 17811 5

I basically want to strip out the dupe. Any thoughts? Thanks

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-21 : 11:40:41

SELECT TOP 1

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-08-21 : 11:53:50

[code]
create table #mytable (intTransactionID int, intAliasID int, Enrollment int)
insert into #mytable
select 8166 ,17854 ,3 union all
select 8121, 17850, 4 union all
select 8142, 17850, 4 union all
select 8223, 17827, 2 union all
select 8149, 17811, 5 union all
select 8128, 17811, 5

select a.*
from #mytable a
left join #mytable b
on a.intAliasID = b.intAliasID and a.intTransactionID < b.intTransactionID
where b.intTransactionID is null

drop table #mytable

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Harry C
Posting Yak Master

148 Posts

Posted - 2008-08-21 : 11:54:29

If I use select TOP 1, I will obviously only get one row back...I need to get all the rows back, just no dups for the given intAliasID....

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-21 : 11:59:49

SELECT DISTINCT

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-21 : 12:22:36

Jhocutt's method works, there is also the 2005 way (this will also handle N number of Alias IDs that are the same, not just 2):

DECLARE @Yak TABLE (intTransactionID int, intAliasID int, Enrollment int)
insert into @Yak
select 8166 ,17854 ,3 union all
select 8121, 17850, 4 union all
select 8142, 17850, 4 union all
select 8223, 17827, 2 union all
select 8149, 17811, 5 union all
select 8128, 17811, 5 


SELECT 
	intTransactionID, 
	intAliasID, 
	Enrollment 
FROM
	(
		SELECT 
			*,
			ROW_NUMBER() OVER(PARTITION BY intAliasID ORDER BY intTransactionID DESC) AS RowNum
		FROM 
			@Yak
	) AS Temp
WHERE
	RowNum = 1

Harry C
Posting Yak Master

148 Posts

Posted - 2008-08-21 : 13:04:57

Thanks folks, both methods worked out. I appreciate the help.

HC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources