How to get 2 Max values?

[Dukhti_Siri]

I have following 2 tables (T1, T2) and rows:

T1 (Column: Id)
----
1
2
3
4

T2 (Columns: Id, Value)
----
1, 1
1, 2
1, 3
1, 4
2, 1
2, 2
2, 3
2, 4
3, 1
3, 2
3, 3
3, 4
4, 1
4, 2
4, 3
4, 4

The result-set I need is:
---------------------------------------
1, 3
1, 4
2, 3
2, 4
3, 3
3, 4
4, 3
4, 4

i.e for each Id from T1, I need 2 max values from T2.

How do I write the query?

[Dukhti_Siri]

Will this work?

Select t1.Id,
( Select t2.Value
From t2
Where t2.Id = t1.Id
Order By Value Desc
Limit 2
)
From t1

I keep getting subquery returns more than one rows.

[Tom Cooper]

Assuming you are on SQL 2005 or later, you want

With cte As
(Select t1.Id,
t2.Value,
Row_Number() Over(Partition By t1.Id Order By t2.Value Desc) As rn
From t1
Inner Join t2 On t1.Id = t2.Id)
Select Id, Value
From cte
Where rn <= 2
Order By Id, rn Desc;

Tom

"Dukhti_Siri" <u64035@uwe> wrote in message news:ad68a93cf999f@uwe...

[Bob Barrows]

Try this (assuming SQL 2005+ - if you're using an earlier version then
you should not forget to specify the version you are using in your
future posts).

;with toptwo as (
select id,max(value) maxvals from T2
group by id
union
select T2.id, max(value) from T2
join (
select id,max(value) maxvalue from T2
group by id) q on T2.id=q.id and value < maxvalue
group by T2.id)
select T1.id,maxvals
FROM T1 join toptwo on T1.id=toptwo.id

Or use Rownumber as Tom suggests (darn! why do I always forget about
that??)
--
HTH,
Bob Barrows

[Dukhti_Siri]

Thanks Tom and Bob, both resolves worked for me

Bob Barrows wrote:
>> I have following 2 tables (T1, T2) and rows:
>>

>[quoted text clipped - 38 lines]