How to use max and group by statement on my SQL query?

task_name         start_time            value
acc_clock         2010-05-27              4
icc_opy           2010-05-28              5
icc_dtaf          2010-05-29 开发者_运维技巧             3
acc_clock         2010-05-25             34
icc_ruty          2010-05-23             33
icc_ruty          2010-05-22             45

This is my output of a SQL query which is coming from two different tables. Note that in this output, task_name is occuring twice. Now I want the output to have only one occurence of task_name and its associated value should be the maximum start_time, like this:

task_name         start_time            value
icc_opy           2010-05-28              5
icc_dtaf          2010-05-29              3
acc_clock         2010-05-25             34
icc_ruty          2010-05-23             33

My query is

select t.task_name, 
            max(t.start_time) , 
            i.value
from    task_runs t,
            integer_values i 
where i.run_id= t.id 
    and t.username= 'amit' 
    and t.start_time > '2010-05-20' 
order by t.task_name 
 group by t.task_name?????????       

---

Why my query is not working?

My values coming from two tables : task_runs, integer_value. I want these three columns but task name with max start_time with associated its value.

---

There are two problems with your current statement

• ORDER BYmust be placed after a GROUP BY
• You either have to add i.value to the GROUP BY clause or use an aggregate function (MIN, MAX, AVG, ...) on i.value

SQL Statement

    select  t.task_name
            , max(t.start_time) 
            , i.value 
    from    task_runs t
            , integer_values i 
    where   i.run_id= t.id 
            and t.username= 'amit' 
            and t.start_time > '2010-05-20' 
    group by 
            t.task_name
            , i.value
    order by 
            t.task_name 

SQL Statement (Edit)

select  t.task_name
        , t.start_time
        , i.value
from    (
          select  t.task_name
                  , start_time = max(t.start_time) 
          from    task_runs t
          where   t.username= 'amit' 
                  and t.start_time > '2010-05-20' 
          group by 
                  t.task_name
        ) tm
        INNER JOIN task_runst t ON t.task_name = tm.task_name AND t.start_time = tm.start_time        
        INNER JOIN integer_values i ON i.run_id = t.id        

order by 
        t.task_name 

---

The GROUP BY must go before ORDER BY and you should choose what value of i.value do you want (max, min, ...).

---

On MSSQL try this

SELECT      t.id
            , MAX(t.task_name) AS task_name
            , MAX(t.start_time) AS start_time
            , (SELECT TOP 1 value FROM integer_values WHERE run_id = t.id) AS value
FROM        task_runs t
WHERE       t.username = 'amit' 
            AND t.start_time > '20100520' 
GROUP BY    t.id
ORDER BY    task_name