July 24, 2006 at 8:15 am
guys
I have table with 3 columns
seq area page
_____________________
1 300 0
1 200 0
1 100 0
1 400 0
2 600 0
2 500 0
2 300 0
2 700 0
I want a query for each sequence which sorts top 3 areas for each sequence. For example in the above table my result set
should be
seq area
_____________
1 400
1 300
1 200
2 700
2 600
2 500
Any suggestions/inputs on writing such query??
Thanks
July 24, 2006 at 9:20 am
I imagine there is a better way but, this seems to work.
select distinct a.seq, a.area from play a inner join play b on a.seq = b.seq and a.area in
(select top 3 c.area from play c where c.seq = a.seq order by c.area desc)
order by a.seq asc, a.area desc
bad cut-paste, no need for the inner join
select distinct a.seq, a.area from play a where a.area in (select top 3 c.area from play c where c.seq = a.seq order by c.area desc) order by a.seq asc, a.area desc
July 24, 2006 at 10:00 am
-- Prepare test data
declare @table table (seq tinyint, area smallint, page tinyint)
insert @table
select 1, 300, 0 union all
select 1, 200, 0 union all
select 1, 100, 0 union all
select 1, 400, 0 union all
select 2, 600, 0 union all
select 2, 500, 0 union all
select 2, 300, 0 union all
select 2, 700, 0
-- Do the work (this is the code you run on your computer).
select t0.seq,
b0.i * isnull(max(t0.area), 0) +
b1.i * isnull(max(t1.area), 0) +
b2.i * isnull(max(t2.area), 0) mids
from (
select seq,
max(area) area
from @table
group by seq
) t0
left join @table t1 on t1.seq = t0.seq and t1.area < t0.area
left join @table t2 on t2.seq = t1.seq and t2.area < t1.area
cross join (select 0 i union all select 1) b0
cross join (select 0 i union all select 1) b1
cross join (select 0 i union all select 1) b2
where b0.i + b1.i + b2.i = 1
group by t0.seq,
b0.i,
b1.i,
b2.i
having b0.i * isnull(max(t0.area), 0) +
b1.i * isnull(max(t1.area), 0) +
b2.i * isnull(max(t2.area), 0) > 0
order by t0.seq,
b0.i * isnull(max(t0.area), 0) +
b1.i * isnull(max(t1.area), 0) +
b2.i * isnull(max(t2.area), 0) DESC
Output is
Seq Area
--- ----
1 400
1 300
1 200
2 700
2 600
2 500
N 56°04'39.16"
E 12°55'05.25"
July 24, 2006 at 10:06 am
Or...
--data
declare @t table (seq int, area int, page int)
insert @t
select 1, 300, 0
union all select 1, 200, 0
union all select 1, 100, 0
union all select 1, 400, 0
union all select 2, 600, 0
union all select 2, 500, 0
union all select 2, 300, 0
union all select 2, 700, 0
--calculation
select seq, area from @t a
where (select count(*) from @t where seq = a.seq and area > a.area) < 3
order by seq, area desc
/*results
seq area
----------- -----------
1 400
1 300
1 200
2 700
2 600
2 500
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply