3 Min values Query
-
Hi
Tab1
Col_Item
Col_Rate
In above table there are several rates for many items.
I want to pic 3 minimum rates for all items.
Examples
Current status
Col_Item Col_Rate
Item1 1
Item1 2
Item1 4
Item1 5
Item2 3
Item2 6
Item2 8
Item2 9
Required query result
Col_Item Min(rate) 2ndMin(Rate) 3rd Min(Rate)
Item1 1 2 4
Item2 3 6 8
Wishes
Jawad
-
Hi Jawad,
Here's one way. Note that it does not cope with ties. Let us know if it needs to.
--data
declare @Tab1 table (Col_Item varchar(10), Col_Rate int)
insert @Tab1
select 'Item1', 1
union all select 'Item1', 2
union all select 'Item1', 4
union all select 'Item1', 5
union all select 'Item2', 3
union all select 'Item2', 6
union all select 'Item2', 8
union all select 'Item2', 9
--calculation
select
Col_Item,
sum(case when position = 1 then Col_Rate else 0 end) as Min,
sum(case when position = 2 then Col_Rate else 0 end) as Min2,
sum(case when position = 3 then Col_Rate else 0 end) as Min3
from (
select a.Col_Item, b.Col_Rate, count(*) as position
from @Tab1 a, @Tab1 b
where a.Col_Item = b.Col_Item and a.Col_Rate <= b.Col_Rate
group by a.Col_Item, b.Col_Rate
) c
group by Col_Item
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply