Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] ORDER BY..?

Expand Messages
  • Razvan Socol
    You cannot order by a column which is calculated using a subquery in Microsoft Access. I see two ways to solve this problem: Solution 1. don t use a subquery;
    Message 1 of 10 , Aug 30 10:45 PM
    • 0 Attachment
      You cannot order by a column which is calculated using a subquery in Microsoft Access. I see two ways to solve this problem:
       
      Solution 1. don't use a subquery; calculate the sum in the query, by joining the required tables and grouping by all the other columns, like this:
       
      Query1:
      SELECT ti.TeamName, di.DivisionName, pi.PlayerRating, pi.FirstName,
          pi.LastName, di.DivisionID, ditm.TeamNumber,
          Sum(MVPPoints) AS SumOfMVPPoints
      FROM ((((((PlayerStatsMap psm
          INNER JOIN PlayerStats ps ON psm.PlayerStatsID = ps.AutoPlayerStatsID)
          INNER JOIN PlayerInfo AS pi ON pi.AutoPlayerID = psm.PlayerInfoID)
          INNER JOIN TeamInfoPlayerMap AS tipm ON tipm.PlayerID=pi.AutoPlayerID)
          INNER JOIN TeamInfo AS ti ON ti.AutoTeamID=tipm.TeamInfoID)
          INNER JOIN DivisionInfoTeamMap AS ditm ON ti.AutoTeamID=ditm.TeamInfoID)
          INNER JOIN DivisionInfo AS di ON di.AutoDivisionID=ditm.DivisionInfoID)
      WHERE di.AutoDivisionID=30
      GROUP BY ti.TeamName, di.DivisionName, pi.PlayerRating, pi.FirstName,
          pi.LastName, di.DivisionID, ditm.TeamNumber
      ORDER BY Sum(MVPPoints) DESC;
       
      I hope I wrote the joins right (I usually build them graphically in Access, but I can't do that without the tables; lately, I wrote joins only in SQL Server, which has a clearer syntax). Anyway, if I made a mistake in the joins, I think you got the idea...
       
      Solution 2. leave this query unsorted and create another query (based on this one) to sort the results.
       
      Query1:
      SELECT ti.TeamName, di.DivisionName, pi.PlayerRating, pi.FirstName,
      pi.LastName, di.DivisionID, ditm.TeamNumber,
      (SELECT Sum(MVPPoints) FROM PlayerStats ps, PlayerStatsMap psm WHERE
      pi.AutoPlayerID = psm.PlayerInfoID AND psm.PlayerStatsID =
      ps.AutoPlayerStatsID) AS MVPPoints
      FROM TeamInfoPlayerMap AS tipm, PlayerInfo AS pi, DivisionInfo AS di,
      TeamInfo AS ti INNER JOIN DivisionInfoTeamMap AS ditm ON
      ti.AutoTeamID=ditm.TeamInfoID WHERE di.AutoDivisionID=30 And
      di.AutoDivisionID=ditm.DivisionInfoID And
      ditm.TeamInfoID=ti.AutoTeamID And ti.AutoTeamID=tipm.TeamInfoID And
      tipm.PlayerID=pi.AutoPlayerID
       
      Query2:
      SELECT * FROM Query1 ORDER BY MVPPoints DESC
       
      There is a difference between the two solutions: the first one doesn't include players that have no PlayerStats, but the second one does. 
       
      Razvan
      ----- Original Message -----
      Sent: Monday, August 30, 2004 5:48 PM
      Subject: [SQLQueriesNoCode] ORDER BY..?

      Hello all,

      I am trying to do an ORDER BY on a field that I am summing up in a
      sub-select. I am using Microsoft Access and my Query looks like this:



      SELECT ti.TeamName, di.DivisionName, pi.PlayerRating, pi.FirstName,
      pi.LastName, di.DivisionID, ditm.TeamNumber,

      (SELECT Sum(MVPPoints) FROM PlayerStats ps, PlayerStatsMap psm WHERE
      pi.AutoPlayerID = psm.PlayerInfoID AND psm.PlayerStatsID =
      ps.AutoPlayerStatsID) AS MVPPoints

      FROM TeamInfoPlayerMap AS tipm, PlayerInfo AS pi, DivisionInfo AS di,
      TeamInfo AS ti INNER JOIN DivisionInfoTeamMap AS ditm ON
      ti.AutoTeamID=ditm.TeamInfoID WHERE di.AutoDivisionID=30 And
      di.AutoDivisionID=ditm.DivisionInfoID And
      ditm.TeamInfoID=ti.AutoTeamID And ti.AutoTeamID=tipm.TeamInfoID And
      tipm.PlayerID=pi.AutoPlayerID ORDER BY Sum(MVPPoints) DESC;


      When I run this Query, Access prompts me for the value of MVPPoints?
      What am I missing here?

      Thanks in advance,
      -Scott
    • Scott Barrett
      Thank you Razvan, that is exactly what I was looking for... ... by joining the required tables and grouping by all the other columns, ... ps.AutoPlayerStatsID)
      Message 2 of 10 , Sep 1, 2004
      • 0 Attachment
        Thank you Razvan, that is exactly what I was looking for...

        --- In SQLQueriesNoCode@yahoogroups.com, "Razvan Socol" <rsocol@f...>
        wrote:
        > You cannot order by a column which is calculated using a subquery
        in Microsoft Access. I see two ways to solve this problem:
        >
        > Solution 1. don't use a subquery; calculate the sum in the query,
        by joining the required tables and grouping by all the other columns,
        like this:
        >
        > Query1:
        > SELECT ti.TeamName, di.DivisionName, pi.PlayerRating, pi.FirstName,
        > pi.LastName, di.DivisionID, ditm.TeamNumber,
        > Sum(MVPPoints) AS SumOfMVPPoints
        > FROM ((((((PlayerStatsMap psm
        > INNER JOIN PlayerStats ps ON psm.PlayerStatsID =
        ps.AutoPlayerStatsID)
        > INNER JOIN PlayerInfo AS pi ON pi.AutoPlayerID =
        psm.PlayerInfoID)
        > INNER JOIN TeamInfoPlayerMap AS tipm ON
        tipm.PlayerID=pi.AutoPlayerID)
        > INNER JOIN TeamInfo AS ti ON ti.AutoTeamID=tipm.TeamInfoID)
        > INNER JOIN DivisionInfoTeamMap AS ditm ON
        ti.AutoTeamID=ditm.TeamInfoID)
        > INNER JOIN DivisionInfo AS di ON
        di.AutoDivisionID=ditm.DivisionInfoID)
        > WHERE di.AutoDivisionID=30
        > GROUP BY ti.TeamName, di.DivisionName, pi.PlayerRating,
        pi.FirstName,
        > pi.LastName, di.DivisionID, ditm.TeamNumber
        > ORDER BY Sum(MVPPoints) DESC;
        >
        > I hope I wrote the joins right (I usually build them graphically in
        Access, but I can't do that without the tables; lately, I wrote joins
        only in SQL Server, which has a clearer syntax). Anyway, if I made a
        mistake in the joins, I think you got the idea...
        >
        > Solution 2. leave this query unsorted and create another query
        (based on this one) to sort the results.
        >
        > Query1:
        > SELECT ti.TeamName, di.DivisionName, pi.PlayerRating, pi.FirstName,
        > pi.LastName, di.DivisionID, ditm.TeamNumber,
        > (SELECT Sum(MVPPoints) FROM PlayerStats ps, PlayerStatsMap psm
        WHERE
        > pi.AutoPlayerID = psm.PlayerInfoID AND psm.PlayerStatsID =
        > ps.AutoPlayerStatsID) AS MVPPoints
        > FROM TeamInfoPlayerMap AS tipm, PlayerInfo AS pi, DivisionInfo AS
        di,
        > TeamInfo AS ti INNER JOIN DivisionInfoTeamMap AS ditm ON
        > ti.AutoTeamID=ditm.TeamInfoID WHERE di.AutoDivisionID=30 And
        > di.AutoDivisionID=ditm.DivisionInfoID And
        > ditm.TeamInfoID=ti.AutoTeamID And ti.AutoTeamID=tipm.TeamInfoID And
        > tipm.PlayerID=pi.AutoPlayerID
        >
        > Query2:
        > SELECT * FROM Query1 ORDER BY MVPPoints DESC
        >
        > There is a difference between the two solutions: the first one
        doesn't include players that have no PlayerStats, but the second one
        does.
        >
        > Razvan
        > ----- Original Message -----
        > From: Scott Barrett
        > To: SQLQueriesNoCode@yahoogroups.com
        > Sent: Monday, August 30, 2004 5:48 PM
        > Subject: [SQLQueriesNoCode] ORDER BY..?
        >
        >
        > Hello all,
        >
        > I am trying to do an ORDER BY on a field that I am summing up in
        a
        > sub-select. I am using Microsoft Access and my Query looks like
        this:
        >
        >
        >
        > SELECT ti.TeamName, di.DivisionName, pi.PlayerRating,
        pi.FirstName,
        > pi.LastName, di.DivisionID, ditm.TeamNumber,
        >
        > (SELECT Sum(MVPPoints) FROM PlayerStats ps, PlayerStatsMap psm
        WHERE
        > pi.AutoPlayerID = psm.PlayerInfoID AND psm.PlayerStatsID =
        > ps.AutoPlayerStatsID) AS MVPPoints
        >
        > FROM TeamInfoPlayerMap AS tipm, PlayerInfo AS pi, DivisionInfo AS
        di,
        > TeamInfo AS ti INNER JOIN DivisionInfoTeamMap AS ditm ON
        > ti.AutoTeamID=ditm.TeamInfoID WHERE di.AutoDivisionID=30 And
        > di.AutoDivisionID=ditm.DivisionInfoID And
        > ditm.TeamInfoID=ti.AutoTeamID And ti.AutoTeamID=tipm.TeamInfoID
        And
        > tipm.PlayerID=pi.AutoPlayerID ORDER BY Sum(MVPPoints) DESC;
        >
        >
        > When I run this Query, Access prompts me for the value of
        MVPPoints?
        > What am I missing here?
        >
        > Thanks in advance,
        > -Scott
      Your message has been successfully submitted and would be delivered to recipients shortly.