Simple query is failing 3

[audiopro]

I have a problem with a query and hope someone can tell me what I am missing. The query is more complicated than this but I have simplified it in order to trace the error.
Both of these work

SELECT MAX(T2) FROM THATFORUM
SELECT T1, T2, CREATED FROM THATFORUM

but his fails.

SELECT T1, T2, CREATED, MAX(T2) FROM THATFORUM

Am I going mad?

Keith

http://www.studiosoft.co.uk

 

[ethorn10]

You need a GROUP BY for the 2nd one to work.

SELECT T1
  , T2
  , CREATED
  , MAX(T2) AS MAXofT2
FROM THATFORUM
GROUP BY T1
  , T2
  , CREATED

 

[audiopro]

Thanks for pointing me in the right direction but I still can't get the result I need.

Here is the query

SELECT T1,T2,T3,T4, MAX(T2), MAX(T3) FROM THATFORUM GROUP BY T1,T2,T3 ORDER BY T1 DESC,T2,T3

and the test results

T1 - T2 - T3 - T4 - MAX(T2) - MAX(T3)
22 1 0 0    1 0 
22 2 0 0    2 0 
22 2 1 0    2 1 
22 3 0 0    3 0 
22 3 1 0    3 1 
22 3 2 0    3 2 
22 4 0 0    4 0

The MAX columns are a 'MAX so far calc' but need the column total in all of the records, not just at the end.

All the MAX(T2) column would = 4 and
all the MAX(T3) column would = 2.
During development I have got the results by additional queries but I am trying to combine it all into 1.
Is this possible?


Keith

http://www.studiosoft.co.uk

 

[r937]

SELECT T1
     , T2
     , T3
     , T4
     , ( SELECT MAX(T2) 
           FROM THATFORUM ) AS MAXT2
     , ( SELECT MAX(T3) 
           FROM THATFORUM ) AS MAXT3
  FROM THATFORUM
ORDER 
    BY T1 DESC
     , T2
     , T3

r937.com | rudy.ca

 

[audiopro]

Thanks very much - that works a treat.


Keith

http://www.studiosoft.co.uk

 

[audiopro]

The full table has groups of records where T1 contains the group number. How can I limit the scope to max the value of T2 within each group?

Keith

http://www.studiosoft.co.uk

 

[r937]

SELECT T1
     , T2
     , T3
     , T4
     , MAXT2
     , ( SELECT MAX(T3) 
           FROM THATFORUM ) AS MAXT3
  FROM THATFORUM AS T
INNER
  JOIN ( SELECT T1
              , MAX(T2) AS MAXT2
           FROM THATFORUM 
         GROUP BY T1 ) AS M2
    ON M2.T1 = T.T1
ORDER 
    BY T1 DESC
     , T2
     , T3

r937.com | rudy.ca

 

[audiopro]

Sorry - I can't get that to work.

( SELECT MAX(T3) FROM THATFORUM ) AS MAXT3 FROM THATFORUM AS T

What is that assigning to T?

Keith

http://www.studiosoft.co.uk

 

[r937]

T is the alias for THATFORUM

it is used here:

ON M2.T1 = T.T1


r937.com | rudy.ca

 

[audiopro]

I cannot get that query to return anything.
Does MySQL have any form of error reporting. I have a log somewhere which logs problems with the server or connection but I cannot find anything for syntax checking. If I put a faulty query on the command line I get an unhelpful 'There was an error' message.

Keith

http://www.studiosoft.co.uk

 

[r937]

does mysql have any form of error reporting?

yes

all of its syntax errors look like:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near:"

and then it shows you the exact word where parsing failed


r937.com | rudy.ca

 

[audiopro]

How do I get to that error message via database commands.
I found mysql_error() but info on it seems a little thin on the ground.

Keith

http://www.studiosoft.co.uk

 

[towerbase]

mysql_error() looks like PHP so strictly speaking you should ask in the PHP forum.

However mysql_error() is fairly informative. For example this SQL statement is erroneous because the field rubbish does not exist in the table:

  $result = @mysql_query("SELECT rubbish FROM sd_way"); 
  if (!$result)
    die ('<h2>' . mysql_error() . '</h2>');

mysql_error() returned the following:

Unknown column 'rubbish' in 'field list'

What more could you ask for?

Andrew
Hampshire, UK

 

[audiopro]

What more indeed.
I am working in the perl environment and a little bit of poking around has revealed the 'errstr'. With that in place I can ID the error as

Column 'T1' in field list is ambiguous

The T1 in question is the one in the first select statement.
I tried replacing T1 with T.T1, which clears the error but returns an empty column.


Keith

http://www.studiosoft.co.uk

 

[audiopro]

Thanks Andrew - that inspired me to search a bit deeper.
The last error was me just being numb - my tired old eyes didn't spot I had put T,T1 instead of T.T1.
The first couple of times I ran the query it didn't throw an error.
Ah well - onwards forever backwards.

Keith

http://www.studiosoft.co.uk