Max(col) / col2 order by

bimmer528

col and col2 are both numbered entries

name, MAX(col) / col2 AS myalias FROM Table
GROUP BY name
ORDER BY myalias DESC

not working. Can I not order by division?

rincewind456

Apart from needing a "select" at the start, that query will work fine

Roger_Ramjet

Don't think it will.

Assuming that you have multiple rows with the same name, your max(col) is going to return the value from one of them, yes. But which col2 value from which row is it going to use in the division?

bimmer528

Multiple rows with the same name is correct Roger but using group by name should combine all duplicate names and then find the MAX of col.

Using my select statement should output col2 in the same row as MAX(col) correct?

My ORDER BY just is not working with the division MAX(col) / col2 AS myalias FROM table GROUP By name ORDER BY myalias DESC

both col and col2 are numbered entries and should divide. Actually, im sure it is dividing no problem but maybe ORDER BY does not see the divided results?

Some example rows I have if I take out the division and just query Max(col) and col2

name-hicky
max(col)-34
col2-8

name-ukam
max(col)-27
col2-10

Roger_Ramjet

I know how the group by works, bimmer.

Your query is an example of group by with hidden fields , I was refering to this part of the manual, perhaps not very clearly:

Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You get unpredictable results.

Now if your query without the division is working and the correct col2 value is always returned then I completely misunderstand it anyway :bemused:

As to ordering by the calculated column, I just ran this

SELECT test, max(uid)/aid as s FROM assign group by test order by s desc

in phpadmin and it worked for me without a hitch. So I've got no idea what the problem is.