On Thu, 5 Aug 2004, Ram Nathaniel wrote: > > 1) The operator "of max": > suppose I have a table "grades" of 3 fields: class/student/grade where I > store many grades of many students of many classes. I want to get the > name of the highest scoring student in each class. Note that there may > be many students with the same grade, but for starters let's say there > is a primary key of class+grade. > > My query would now be: > select student from grades where class+'#'+grade in > ( > select class+'#'+max(grade) from grades group by class > ) a
As a side note, I'd think that something like: select student from grades where (class,grade) in (select class, max(grade) from grades group by class); should avoid textual operations. I'm assuming the + above are meant to be concatenation (||). > The optimal would be to introduce a new operator "of max" that would be used as > follows: > > select student of max(grade) from grades group by class PostgreSQL provides an extension called DISTINCT ON. Something like select distinct on (class) student from grades order by class, grade desc; should get you one arbitrary student with the highest grade in his or her class. If you want to order by the grades, I think you need a layer around it. If you don't care about the class order, you might consider making the class ordering desc as well to make it easier to use a multi-column index on (class,grade). > 2) aggregated concatenation: Theoretically, you should be able to do this right now in PostgreSQL with user defined aggregates (although you can't pass a second argument currently for the separator). I believe that an ordered subquery in FROM will currently allow you to get an ordered aggregate, or perhaps you'd have to turn off hash aggregation, but I think you should be able to get it to keep the ordering. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
