Ul
>
> I am not an expert in SQL, but I fount this:
>
> http://rickosborne.org/blog/2008/01/sql-getting-top-n-rows-for-a-grouped-query/
>
> Maybe it'll give you some hints how to proceed.
>
> Regards Uli

A very elegant solution.

John

John,

> A very elegant solution.

I guess it was written by you using a pseudonym.

Regards Uli

Uli Becker" <johnmuller54@googlemail.com> wrote in message
news:BF2096F3-14C7-43B1-BC65-7D7857D379BD@news.elevatesoft.com...
> John,
>
> > A very elegant solution.
>
> I guess it was written by you using a pseudonym.

I wish

The use of a cartesian join in the solution did get me interested.  For all but really large datasets there is no issue.
If you do have a lot of different rows in each category (and I mean a lot!) it is possibble to use a combination of
EDB's RUNSUM() and MIN() functions to get the same result, quicker without resorting to a join.  The resulting sql is
(nearly) unintelligible.

Cheers

John

John


Post it - I want to test my reading skills

Roy Lambert

Roy

> Post it - I want to test my reading skills

Have fun

John

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

John


You are one seriously sad individual

Roy Lambert

ps reading skills about year 2

> 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

Depressing!

Uli

Over quoting


Roy Lambert

Sorry for being so late (was renovating my house and was not on Delphi for quite some time):
Thank you very much!

Lucian

>Have fun

Thank you John, it did the trick!
Lucian