[SOLVED] Returning the highest single value.

[lemmin]

I have two tables that I want to query from. They would look something like this:

 

Table1

Field1 | Field2

1 | 1

2 | 1

3 | 2

4 | 3

5 | 3

6 | 4

 

Table2

Field1 | Field2

1 | 1

2 | 1

3 | 2

4 | 3

5 | 4

6 | 4

 

Basically just an auto numbered first field and categories in the second one. The problem is, I want to all values in field1 from the first table, and get one value (the highest) from field1 in the second table, where field 2 matches the first table's field1. So, for the first row I want it to return: 1,1 (from first table) and 2, 1 from second, because 2 is higher than 1.

 

I can do this with something like:

 

"SELECT table1.Field1, table2.Field1 From table1, table2 WHERE table2.Field2 = table1.Field1 GROUP BY table1.Field1"

 

But I can't find a way to get only the highest value. It just returns the first value that matches. I tried to use ORDER BY, but it only doesn't order before checking the WHERE clause.

 

Thanks for any help.

[akitchin]

you could use the MAX() function here.  assuming i've understood your problem correctly, try this:

 

SELECT MAX(t2.field1) AS t2f1, t1.field1 AS t1f1 FROM table2 t2 INNER JOIN table1 t1 ON t1.field1=t2.field2 WHERE t1.field1 IS NOT NULL GROUP BY t2.field2 ORDER BY t2.field2

 

i rarely nail these on the first shot, but it's worth a try.  this should, in principle, select the maximum value of field1 in table2 for each field2 value in table2, restricting it to only situations where table1's field1 isn't null for that field2 in table 2.

 

it is even easier to write this query if you intend to restrict the table1 field2 to a single explicit value (rather than cycling through each table1 field1).

[lemmin]

Yes that does work, thank you. However, I now need to implement another field that is text. So how could I return anything in the row that corresponds to the number that is returned as the max without making a new query?

 

Thanks for helping.

[akitchin]

if you mean grabbing another field from the row in table2 that matches the MAX(), then simply add it to the list of fields to SELECT:

 

SELECT MAX(t2.field1) AS t2f1, t2.textfield AS textfield, t1.field1 AS t1f1 FROM table2 t2 INNER JOIN table1 t1 ON t1.field1=t2.field2 WHERE t1.field1 IS NOT NULL GROUP BY t2.field2 ORDER BY t2.field2

[lemmin]

That will return the first text field that matches the join criteria. It will be from a different row than the max number. I tried using something like table2field3 = max(table2.field1) but it gives an error about invalid use of GROUP.

[akitchin]

have you run the query to verify that it will pull the text field with the first join match, or is that speculation?  i'm under the impression that it will only grab one row for each table1 field1 that exists.  if i'm wrong, then try using the max field in the where clause:

 

SELECT MAX(t2.field1) AS t2f1, t2.textfield AS textfield, t1.field1 AS t1f1 FROM table2 t2 INNER JOIN table1 t1 ON t1.field1=t2.field2 WHERE t1.field1 IS NOT NULL AND t2.field1=t2f1 GROUP BY t2.field2 ORDER BY t2.field2

 

however this strikes me as being redundant.

[lemmin]

Yes I have tested it. It won't let me use the max function in the WHERE clause and if I try to use the variable, it says unknown column.

[akitchin]

right - that's because the WHERE clause is used to calculate the aggregate function values, and as such the aggregate value(s) are unavailable to the WHERE clause.  you must use the GROUP BY clause:

 

SELECT MAX(t2.field1) AS t2f1, t2.textfield AS textfield, t1.field1 AS t1f1 FROM table2 t2 INNER JOIN table1 t1 ON t1.field1=t2.field2 WHERE t1.field1 IS NOT NULL GROUP BY t2.field2 HAVING t2.field1=t2f1 ORDER BY t2.field2

 

give that a shot.  if you don't need to actually SELECT either t2.field1 or t1.field1 (ie. you just need to use them for the conditions, but you don't need to know their actual values), you might as well drop them from the SELECT list, remembering to revert the aliases used elsewhere to their full designation.

[lemmin]

If I don't use the exact same field that is in the have clause before it i nthe group clause, it says unknown table. So,

GROUP BY t2.field2 HAVING t2.field1=t2f1 doesn't work, but

GROUP BY t2.field1 HAVING t2.field1=t2f1 does.

 

Which may be fine, but it groups before it calculates the max, so it is finding the max of one single number that is the current row. Would a sub query be something that might help make this work?

[lemmin]

I got it to work!

I probably should have posted my actual code before, but I thought it would have been too confusing (and make me look stupid for trying to query like this), but, this is how I got it to work (With a subquery).

"SELECT topics.Title, topics.Descr, threads.Title as ThreadTitle From topics, threads WHERE threads.TopicID = topics.TopicID AND threads.ThreadID = (SELECT ThreadID FROM threads WHERE threads.TopicID = topics.TopicID ORDER BY threads.ThreadID DESC LIMIT 1) ORDER BY topics.TopicID"

 

I realized that max wouldn't work because it has to be the max out of the values that have the same topicId, not just all in the column.

 

Thanks for all your help, akitchin.