A query to join only one record even if there are more of them



Common questions about 1C:Query language, Query builder tool and Data composition schema

#1
People who like this:0Yes/0No
Active user
Rating: 6
Joined: Sep 16, 2011
Company: TLG Integration

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?

 
#2
People who like this:1Yes/0No
Just came
Rating: 0
Joined: Sep 3, 2012
Company:

Somehow

Code
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";


But all the awards must have different ratings.

 
#3
People who like this:0Yes/0No
Active user
Rating: 7
Joined: Sep 26, 2012
Company: individual

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

 
#4
People who like this:0Yes/0No
Active user
Rating: 6
Joined: Sep 16, 2011
Company: TLG Integration

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.

Edited: Silvia Ashley - Dec 11, 2012 11:49 AM
 
Subscribe