0

I have two tables: task_runs and integer_values. I am running a query to get this output:

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. But I want the output to have only one occurrence 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 doesn't it work?

skink
  • 5,133
  • 6
  • 37
  • 58
Navrattan Bansal
  • 169
  • 1
  • 8
  • Check this link http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql May be its help u – Naresh Sep 29 '11 at 07:25
  • @user979553 can you post the query you used to get the first result set? – Vishwanath Dalvi Sep 29 '11 at 07:29

4 Answers4

1
SELECT task_name, MAX(start_time) FROM your_table GROUP BY task_name;

Group by will take duplicates and group them in this case on task_name. And MAX will take the highest value. SO: when more tasks exist with the same name, display it once and show only the one with the highest start_time.

JNDPNT
  • 7,445
  • 2
  • 34
  • 40
  • This actually wont necessarily show the value that is associated with the MAX(start_time)... just any value. – Konerak Sep 29 '11 at 08:17
0

I think you need to take a look at GROUP BY and MAX

Bas Slagter
  • 9,831
  • 7
  • 47
  • 78
0
SELECT * FROM yourTable y
JOIN 
 (select task_name, MAX(start_time) maxtime FROM yourTable GROUP BY task_name) highest
ON y.task_name = highest.task_name AND y.start_time = highest.start_time
Konerak
  • 39,272
  • 12
  • 98
  • 118
  • This query's performance is horrible. Can you please explain why my solution doesn't work or why you are choosing this approach? – JNDPNT Sep 29 '11 at 07:30
  • Your query works, if he only wants task_name and start_time. But if he wants value? – Konerak Sep 29 '11 at 07:36
  • I am sorry, but that will not work. The value returned will not always be the value that is in the same row as the max_time. – Konerak Sep 29 '11 at 07:41
0
;WITH max_start AS
(
SELECT 
task_name
,start_time
,value
,row_number() OVER (PARTITION BY taskname ORDER BY start_time DESC) as row
FROM your_table
)
SELECT 
task_name
,start_time
,value
FROM max_start 
WHERE row = 1
Dibstar
  • 2,334
  • 2
  • 24
  • 38