CODEWITHSUNDEEP
sqlpostgresql
Navrattan Bansal
Navrattan Bansal

Reputation: 169

How to get maximum value from output of SQL query

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?

Upvotes: 0

Views: 1085

Answers (4)

Dibstar
Dibstar

Reputation: 2364

;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

Upvotes: 0

JNDPNT
JNDPNT

Reputation: 7465

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.

Upvotes: 1

Konerak
Konerak

Reputation: 39763

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

Upvotes: 0

Bas Slagter
Bas Slagter

Reputation: 9929

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

Upvotes: 0

Related Questions