Alex

I found the post from John Hay. It was in the ElevateDB.sql newsgroup in response to this question

I have this query:

SELECT
 EXTRACT(YEAR FROM XDate) as YearNo,
 EXTRACT(MONTH FROM XDate) as MonthNo,
 SUM(IntNumber) as IntNumber,
 UserName
FROM jobs
GROUP BY YearNo, MonthNo, UserName
ORDER BY YearNo, MonthNo, IntNumber DESC

It returns something like this:

2007 6 500 Paul....
2007 6 399 Randy...
2007 6 345 Tom.....
2007 6 299 I don't want this row
2007 6 295 I don't want this row
2007 6 256 I don't want this row
2007 5 800 Greg....
2007 5 689 Zoran...
2007 5 556 Edy.....
2007 5 398 I don't want this row
2007 5 201 I don't want this row
2007 4 700 Greg....
2007 4 449 Randy...
2007 4 256 Tom.....
2007 4 198 I don't want this row
2007 4 101 I don't want this row

However, I only want only the top 3 rows for each group Year, Month. Is it possible in SQL?

select yearno,monthno,intnumber,username,temp-lowest+1 as rank from
 (select yearno,monthno,username,intnumber,runsum(1) as temp from
   (select
      extract(year from xdate) as yearno,
      extract(month from xdate) as monthno,
      Username,sum(intnumber) as intnumber from jobs
    group by yearno,monthno,username
    order by yearno,monthno,intnumber desc) t1
  group by yearno,monthno,username,intnumber
  order by yearno,monthno,intnumber desc) t2
join
 (select yearno,monthno,min(temp) as lowest from
   (select yearno,monthno,username,intnumber,runsum(1) as temp from
     (select
        extract(year from xdate) as yearno,
        extract(month from xdate) as monthno,
        Username,sum(intnumber) as intnumber from jobs
      group by yearno,monthno,username
      order by yearno,monthno,intnumber desc) t3
    group by yearno,monthno,username,intnumber) t4
  group by yearno,monthno) t5
on t5.yearno=t2.yearno and t5.monthno=t2.monthno
group by yearno,monthno,username,intnumber,rank
having rank < 4
order by yearno desc,monthno desc,rank


Roy Lambert