Major issue for me using EDB 2.11b3.  Not sure if problem is in the component or between keyboard and chair.

TEST
ID   |  SCORE |  TRIES
1    |  11008    |  50
1    |  3008     | 15
2   |  2000     | 10
3   |  4000    | 20

Goal is to display the row with that has the highest average for each ID and the average used.
I have a query as

SELECT *, MAX(SCORE/TRIES) AS PersonAve
FROM
   TEST
Group By
 ID

When I execute the query I get

1 | 3008 | 15 | 220.16
2 | 2000 | 10 | 200
3| 4000 | 20 | 200

For ID 2 and 3, I get the expected results based on the data, because they are only in once.

For ID 1, I'm not getting the expected results, other than the average being correct. What I'm expecting/desiring to see is:
1 | 11008 | 50 | 220.16      (The Tries and scores that made up the 220.16)

I ran this test in MySQL and got the correct results of
1 | 11008 | 50 | 220.16

Suggestion?

Lance

Hi Lance,

Would this work???

SELECT TEST.ID, MAX(SCORE) AS SCORE, MAX(TRIES) AS TRIES,
MAX(SCORE/TRIES) AS PersonAve
FROM TEST
Group By ID

Cheers,

Phil.


On 31/01/2013 8:26 AM, Lance Rasmussen wrote:
> Major issue for me using EDB 2.11b3.  Not sure if problem is in the component or between keyboard and chair.
>
> TEST
> ID   |  SCORE |  TRIES
> 1    |  11008    |  50
> 1    |  3008     | 15
> 2   |  2000     | 10
> 3   |  4000    | 20
>
> Goal is to display the row with that has the highest average for each ID and the average used.
> I have a query as
>
> SELECT *, MAX(SCORE/TRIES) AS PersonAve
> FROM
>      TEST
> Group By
>    ID
>
> When I execute the query I get
>
> 1 | 3008 | 15 | 220.16
> 2 | 2000 | 10 | 200
> 3| 4000 | 20 | 200
>
> For ID 2 and 3, I get the expected results based on the data, because they are only in once.
>
> For ID 1, I'm not getting the expected results, other than the average being correct. What I'm expecting/desiring to see is:
> 1 | 11008 | 50 | 220.16      (The Tries and scores that made up the 220.16)
>
> I ran this test in MySQL and got the correct results of
> 1 | 11008 | 50 | 220.16
>
> Suggestion?
>
> Lance
>

Phil


>Would this work???
>
>SELECT TEST.ID, MAX(SCORE) AS SCORE, MAX(TRIES) AS TRIES,
>MAX(SCORE/TRIES) AS PersonAve
>FROM TEST
>Group By ID

It will work for those occasions when MAX(tries) is on the same line as MAX(Score). But for complex cases where That's not the case then no.

Lance - if Phil's solution isn't acceptable what are the rules - if you have 2+ lines for one person with the same score which tries should be used?


Roy Lambert [Team Elevate]

From the information I received from Tim, it looks like the results just happen to occur correctly with MySQL.  Columns in grouped queries that are not aggregates or part of the grouping reflect the last row processed in the grouping, with EDB.  

MySQL possibly chooses the first row processed, or it's a random choice.

Goal was to get the row's info based on the row with the highest average.  

So I can pass on info for future readers, here is what Tim worked up for me that seems to give me the correct results:

SELECT ID, Score, Tries, CAST(Score/Tries AS NUMERIC(19,4)) AS PersonAve
FROM test a
WHERE CAST(Score/Tries AS NUMERIC(19,4)) = (SELECT MAX(CAST(Score/Tries AS NUMERIC(19,4))) FROM test b WHERE b.ID = a.ID)

Also note that it isn't a bug in terms of choosing last or first in the result. This happens to fall into the SQL rules which actually discourage these types of queries.

Here is another way of doing the same thing that seems to work - if you have add a new field as a primary key.

SELECT ID, Tries, Score, MaxAverage
 FROM Test T1
   INNER JOIN

  (SELECT PrimaryID, MAX(Score/Tries) MaxAverage
 FROM Test
 GROUP BY ID) T2

  ON T1.PrimaryId = T2.PrimaryId

Richard Harding

Thanks.

What I ended up doing was creating a temporary table, selecting a joined table (example was simplified to what I'm really doing) filtering out the records not needed, then doing a left outer query against the temp table in the form of.


SELECT ALL
"p"."PersonNum" AS "PersonNum",
"p"."FirstName" AS "FirstName",
"p"."LastName" AS "LastName",
"p"."HighestScore" AS "HighestScore",
"p"."HighestAverage" AS "HighestAverage"
FROM "TMP" AS "p"
LEFT OUTER JOIN "TMP" AS "t2"
ON "p"."PersonNum" = "t2"."PersonNum" AND (("p"."HighestAverage" <
"T2"."HighestAverage") OR ("p"."HighestAverage" = "t2"."HighestAverage" AND
("p"."GroupID" < "t2"."GroupID")))
WHERE "t2"."PersonNum" IS NULL
ORDER BY "p"."HighestAverage" DESC

What was thought to have worked, ended up still filtering out some records incorrectly.

Lance


Richard Harding wrote:

Here is another way of doing the same thing that seems to work - if you have add a new field as a primary key.

SELECT ID, Tries, Score, MaxAverage
 FROM Test T1
   INNER JOIN

  (SELECT PrimaryID, MAX(Score/Tries) MaxAverage
 FROM Test
 GROUP BY ID) T2

  ON T1.PrimaryId = T2.PrimaryId

Richard Harding