Forum

Long time no see! How are you doing?

Here is the problem: I have a list of awards for managers and I would like to select the most important one and display it next to the Description column.

The awards are references to Awards catalog which are stored in Awards tabular section of each manager. The Awards catalog items have the following attributes: Description and Rating as Numeric.

I'm confused with creating a query which will select only one record by a condition. In this case I need to select awards of each employee with the biggest Rating value.

Could you help me with the query?

Somehow

SEL ECT
   ManagersAwards.Ref AS Manager,
   ManagersAwards.Award AS Award
FROM
   Catalog.Managers.Awards AS ManagersAwards
   JOIN ( SELECT
      ManagersAwards.Ref,
      MAX(ManagersAwards.Award.Rating) AS Rating
   FR OM
      Catalog.Managers.Awards AS ManagersAwards
   GROUP BY ManagersAwards.Ref
   ) AS MaxManagersAwards
   ON ManagersAwards.Ref = MaxManagersAwards.Manager
   AND ManagersAwards.Award.Rating = MaxManagersAwards.Rating";

Like advise or suggestion, you can add Attribute "ImportantAward" for catalog Manager, and at BeforeWrite event handler write value for this attribute. If you use "most important award" not once, so it may be more effective

Thank you, Dmitrij! I've got the point!
The attribute Important is interesting too, Ivan, I'll thing about it, but it changes too often to pay attention to setting this value manually.