Query problem (Alias)

[Erakis]

Hi,

I have some problem to build/execute my query :

UPDATE IGNORE StationsSensorsValuesAVG 
SET ssva_Reserve_0_4 = (
       SELECT MIN(ssv_Value) 
       FROM StationsSensorsValues 
       WHERE ssv_StationID = 4 AND      
             ssv_SensorID = 27 AND       
             ssv_Year = 2004 AND       
             ssv_Month = 1 AND       
             ssv_Day = 1 AND       
             HOUR(StationsSensorsValues.ssv_Time) =       
             HOUR(             
                   (SELECT ResultTime 
                    FROM (             
                           SELECT ssv_Time As ResultTime, MAX(ssv_Value) AS ResultValue              
                           FROM StationsSensorsValues
                           WHERE ssv_StationID = 4 AND                   
                                 ssv_SensorID = 28 AND                    
                                 ssv_Year = 2004 AND                    
                                 ssv_Month = 1 AND                    
                                 ssv_Day = 1 AND                    
                                 ssv_Time BETWEEN '00:00:00' AND '03:59:59')              
                           GROUP BY ssv_StationID,                       
                                    ssv_SensorID,                       
                                    ssv_Year,                       
                                    ssv_Month,                       
                                    ssv_Day,                       
                                    HOUR( ssv_Time )              
                           ORDER BY ResultValue DESC LIMIT 1       
                         ) AS SubTable1
                   ) AS SubTable2
             ) 
       ) AS SubTable3, 
ssva_ToBeProceed = 0       
WHERE ssva_DateEntry = '2004-01-01' AND ssva_StationID = 4;

I got the Every derived table must have its own alias error. I tried to add alias but no success. Maybe I'm doing something wrong ?

Thanks

 

[r937]

count your parentheses you have an extra )

r937.com | rudy.ca

 

[Erakis]

Thanks r937,

It was a past code error, but fortunately I found the error by myelf.

Here is the working query ;

UPDATE IGNORE StationsSensorsValuesAVG 
SET ssva_Reserve_0_4 = 
(

       SELECT MIN(ssv_Value) 
       FROM StationsSensorsValues 
       WHERE ssv_StationID = 4 AND      
             ssv_SensorID = 27 AND       
             ssv_Year = 2004 AND       
             ssv_Month = 1 AND       
             ssv_Day = 1 AND       
             HOUR(StationsSensorsValues.ssv_Time) =       
             HOUR
             (             
                    (
                     SELECT ResultTime 
                     FROM 
                         (             
                          SELECT ssv_Time As ResultTime, MAX(ssv_Value) AS ResultValue              
                          FROM StationsSensorsValues AS SubTableResultMax
                          WHERE ssv_StationID = 4 AND                   
                                ssv_SensorID = 28 AND                    
                                ssv_Year = 2004 AND                    
                                ssv_Month = 1 AND                    
                                ssv_Day = 1 AND                    
                                ssv_Time BETWEEN '00:00:00' AND '03:59:59'              
                          GROUP BY ssv_StationID,                       
                                   ssv_SensorID,                       
                                   ssv_Year,                       
                                   ssv_Month,                       
                                   ssv_Day,                       
                                   HOUR( ssv_Time )              
                          ORDER BY ResultValue DESC LIMIT 1
                         ) AS SubTableResultTime
                    )
             )
       ),
ssva_ToBeProceed = 0       
WHERE ssva_DateEntry = '2004-01-01' AND ssva_StationID = 4;

However, I would like to know if this query is optimisable when I do SELECT ResultTime. Because I would like that sub query SELECT ssv_Time As ResultTime, MAX(ssv_Value) AS ResultValue returns only the TIME of the greater value found in the MAX(ssv_Value) list.

Thanks for your help