尝试对查询按组排名

Question

我有一个查询，在这个查询中，我尝试按职位分组，并按最终值排名。

SELECT qryProspects.[Prospect Name]
       , qryProspects.Position
       , qryProspects.Height
       , qryProspects.Weight
       , qryProspects.College
       , qryProspects.RoundID
       , Round([AvgOfAggregate],2) AS [True Value]
       , Round([AvgOfAggregate]*[Multiplier]*[NeedMultiplier],2) AS [Final Value]
       , qryProspects.ProspectID    
  FROM qryProspects
       INNER JOIN qryCalculations ON qryProspects.ProspectID = qryCalculations.ProspectID;

因此，结果将从1开始对所有的QB进行排序，然后是所有的RB，然后是所有的WR，依此类推。

任何帮助都是非常感谢的！

Answer 1

使用派生表。尝尝这个

SELECT [Prospect Name]
       , Position
       , Height
       , Weight
       , College
       , RoundID
       , [True Value]
       , [Final Value]
       , ProspectID
  FROM (SELECT qryProspects.[Prospect Name]
               , qryProspects.Position
               , qryProspects.Height
               , qryProspects.Weight
               , qryProspects.College
               , qryProspects.RoundID
               , Round([AvgOfAggregate],2) AS [True Value]
               , Round([AvgOfAggregate]*[Multiplier]*[NeedMultiplier],2) AS [Final Value]
               , qryProspects.ProspectID    
          FROM qryProspects
               INNER JOIN qryCalculations ON qryProspects.ProspectID = qryCalculations.ProspectID)
    AS QB_ORDER
 GROUP BY Position
 ORDER BY [Final Value]