select two highest value per foreign key

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

select two highest value per foreign key

Author

Topic

christina_rules
Starting Member

23 Posts

Posted - 2008-03-25 : 11:08:15

Hi,

I need help on this one. Let's say I have a table like this:

Table_ID Value SomeOtherTable_ID
1 2 1
2 4 1
3 1 2
4 5 3
5 3 2
6 0 1

How can I get only two rows of each SomeOtherTable_ID? The result that I want is this:

SomeOtherTable_ID Value
1 4
1 2
2 3
2 1
3 5

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:12:42

Assuming you are using SQL Server 2005

DECLARE	@Sample TABLE (TableID TINYINT, Value TINYINT, OtherID TINYINT)

INSERT	@Sample
SELECT	1, 2, 1 UNION ALL
SELECT	2, 4, 1 UNION ALL
SELECT	3, 1, 2 UNION ALL
SELECT	4, 5, 3 UNION ALL
SELECT	5, 3, 2 UNION ALL
SELECT	6, 0, 1

SELECT	OtherID,
	Value
FROM	(
		SELECT	OtherID,
			Value,
			ROW_NUMBER() OVER (PARTITION BY OtherID ORDER BY Value DESC) AS RecID
		FROM	@Sample
	) AS d
WHERE	RecID <= 2

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 15:09:44

Are you sure you want the greatest two values for each id?If you just want two random values per id change like this:-

SELECT	OtherID,
	Value
FROM	(
		SELECT	OtherID,
			Value,
			ROW_NUMBER() OVER (PARTITION BY OtherID ORDER BY NEWID() DESC) AS RecID
		FROM	@Sample
	) AS d
WHERE	RecID <= 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources