Hi,

Suppose I have a table like this:


person, test_score, test_name
"John", 100, "Math"
"John", 50, "Art"
"John", 20, "Sport"
"Mike", 0, "Math"
"Mike", 100, "Art"
"Mike", 10, "Sport"
"Paul", 90, "Math"
"Paul", 80, "Art"


I want to do this:

For each person, select the test with the highest score. For the
example above, the output should be:

"John", 100, "Math"
"Mike", 100, "Art"
"Paul", 90, "Math"

How can I write the query?

Thanks,

fireman

On Fri, 22 Oct 2010 16:06:54 -0700, fireman wrote:

On Fri, 22 Oct 2010 16:06:54 -0700, fireman wrote:

with mscore as (
select person,
test_score,
test_name,
max(test_score) over (partition by person) as max_score
from tab)
select person,test_score,test_name
from mscore where test_score=max_score;

This requires Postgres 8.4 or newer.



--
http://mgogala.byethost5.com

On 2010-10-23 01:06, fireman wrote:
with T1 (person, test_score, test_name)
as ( Values
('John', 100, 'Math')
,('John', 50, 'Art')
,('John', 20, 'Sport')
,('Mike', 0, 'Math')
,('Mike', 100, 'Art')
,('Mike', 10, 'Sport')
,('Paul', 90, 'Math')
,('Paul', 80, 'Art')
)
select * from T1
where (person, test_score) in
(select person, max(test_score) from T1 group by person)
;

------------------------------------
person test_score test_name
Paul 90 Math
Mike 100 Art
John 100 Math

On Sun, 24 Oct 2010 10:00:07 +0200, danfan46 wrote:

This too requires PostgreSQL 8.4 or newer because of the "with" clause.
My solution is a bit faster:

mgogala=# with T1 (person, test_score, test_name)
mgogala-# as ( Values
mgogala(# ('John', 100, 'Math')
mgogala(# ,('John', 50, 'Art')
mgogala(# ,('John', 20, 'Sport')
mgogala(# ,('Mike', 0, 'Math')
mgogala(# ,('Mike', 100, 'Art')
mgogala(# ,('Mike', 10, 'Sport')
mgogala(# ,('Paul', 90, 'Math')
mgogala(# ,('Paul', 80, 'Art')
mgogala(# )
mgogala-# select * from T1
mgogala-# where (person, test_score) in
mgogala-# (select person, max(test_score) from T1 group by person)
mgogala-# ;
person | test_score | test_name
--------+------------+-----------
Paul | 90 | Math
Mike | 100 | Art
John | 100 | Math
(3 rows)

Time: 0.639 ms
mgogala=# with T1 (person, test_score, test_name)
mgogala-# as ( Values
mgogala(# ('John', 100, 'Math')
mgogala(# ,('John', 50, 'Art')
mgogala(# ,('John', 20, 'Sport')
mgogala(# ,('Mike', 0, 'Math')
mgogala(# ,('Mike', 100, 'Art')
mgogala(# ,('Mike', 10, 'Sport')
mgogala(# ,('Paul', 90, 'Math')
mgogala(# ,('Paul', 80, 'Art')
mgogala(# )
mgogala-# select person,test_score,test_name
mgogala-# from (select person,
mgogala(# test_score,
mgogala(# test_name,
mgogala(# max(test_score) over (partition by person) as
max_score
mgogala(# from T1) as T2
mgogala-# where test_score=max_score;
person | test_score | test_name
--------+------------+-----------
John | 100 | Math
Mike | 100 | Art
Paul | 90 | Math
(3 rows)

Time: 0.546 ms

That is the case because window functions are generally faster than self
join. Here are the respective plans:

mgogala=# explain analyze
mgogala-# with T1 (person, test_score, test_name)
mgogala-# as ( Values
mgogala(# ('John', 100, 'Math')
mgogala(# ,('John', 50, 'Art')
mgogala(# ,('John', 20, 'Sport')
mgogala(# ,('Mike', 0, 'Math')
mgogala(# ,('Mike', 100, 'Art')
mgogala(# ,('Mike', 10, 'Sport')
mgogala(# ,('Paul', 90, 'Math')
mgogala(# ,('Paul', 80, 'Art')
mgogala(# )
mgogala-# select * from T1
mgogala-# where (person, test_score) in
mgogala-# (select person, max(test_score) from T1 group by person)
mgogala-# ;
QUERY
PLAN

--------------------------------------------------------------------------------
--------------------------------
Hash Join (cost=0.58..0.83 rows=8 width=68) (actual time=0.091..0.100
rows=3 l
oops=1)
Hash Cond: ((t1.person = t1.person) AND ((max(t1.test_score)) =
t1.test_score
))
CTE t1
-> Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=68)
(actual ti
me=0.008..0.016 rows=8 loops=1)
-> HashAggregate (cost=0.20..0.30 rows=8 width=36) (actual
time=0.021..0.02
4 rows=3 loops=1)
-> CTE Scan on t1 (cost=0.00..0.16 rows=8 width=36) (actual
time=0.00
1..0.006 rows=8 loops=1)
-> Hash (cost=0.16..0.16 rows=8 width=68) (actual time=0.048..0.048
rows=8
loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> CTE Scan on t1 (cost=0.00..0.16 rows=8 width=68) (actual
time=0.01
2..0.035 rows=8 loops=1)
Total runtime: 0.180 ms
(10 rows)

Time: 12.203 ms
mgogala=# explain analyze
mgogala-# with T1 (person, test_score, test_name)
mgogala-# as ( Values
mgogala(# ('John', 100, 'Math')
mgogala(# ,('John', 50, 'Art')
mgogala(# ,('John', 20, 'Sport')
mgogala(# ,('Mike', 0, 'Math')
mgogala(# ,('Mike', 100, 'Art')
mgogala(# ,('Mike', 10, 'Sport')
mgogala(# ,('Paul', 90, 'Math')
mgogala(# ,('Paul', 80, 'Art')
mgogala(# )
mgogala-# select person,test_score,test_name
mgogala-# from (select person,
mgogala(# test_score,
mgogala(# test_name,
mgogala(# max(test_score) over (partition by person) as
max_score
mgogala(# from T1) as T2
mgogala-# where test_score=max_score;
QUERY
PLAN

--------------------------------------------------------------------------------
--------------------------------
Subquery Scan on t2 (cost=0.38..0.62 rows=1 width=72) (actual
time=0.065..0.08
7 rows=3 loops=1)
Filter: (t2.test_score = t2.max_score)
CTE t1
-> Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=68)
(actual ti
me=0.002..0.009 rows=8 loops=1)
-> WindowAgg (cost=0.28..0.42 rows=8 width=68) (actual
time=0.060..0.076 ro
ws=8 loops=1)
-> Sort (cost=0.28..0.30 rows=8 width=68) (actual
time=0.046..0.049 r
ows=8 loops=1)
Sort Key: t1.person
Sort Method: quicksort Memory: 17kB
-> CTE Scan on t1 (cost=0.00..0.16 rows=8 width=68)
(actual tim
e=0.007..0.024 rows=8 loops=1)
Total runtime: 0.136 ms
(10 rows)

Time: 0.874 ms
mgogala=#



--
http://mgogala.byethost5.com