From: | "Peter Alberer" <h9351252(at)obelix(dot)wu-wien(dot)ac(dot)at> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Difficult query |
Date: | 2003-03-05 16:02:42 |
Message-ID: | 001d01c2e330$a7337d20$5be0d089@ekelhardt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
i have been thinking about the following query for some time but cannot
find a good answer.
I have a table called lr_area_stats__user_day that holds info about the
number of exercises a student has worked on in a certain area on a
certain day. The "area" is a short name for a course for example
"business admin" -> "ba". The columns total and succ tell about the
numbers of exercises solved and the number of successful solutions.
create table lr_area_stats__user_day (
area varchar(5),
user_id integer
constraint area_stats_user_fk
references persons(person_id)
on delete cascade,
day date,
total integer,
succ integer,
distinct_excs integer
);
I would like to get the "best" student for the last 14 days per area in
one query, but all I can get is all of the entries per area and student.
My query looks like that:
select area, user_id, sum(total) as total, sum(succ) as succ
from lr_area_stats__user_day
where day >= (now() - '14 day'::interval)::date
group by area, user_id
order by area asc, succ desc, total desc
The output has all the info I need but tons of entries I want to filter
out:
vw1 | 258864 | 1 | 0
vw1 | 258925 | 1 | 0
wigeo | 15840 | 6 | 0 <--
wigeo | 251229 | 4 | 0
wipr | 147405 | 818 | 776 <-- want to get the top entries per area
wipr | 140616 | 1174 | 734
wipr | 150895 | 808 | 591
wipr | 136125 | 621 | 542
wipr | 149796 | 627 | 526
How can I filter the output to only return the top values per area? I
would prefer not to use stored-procedures or client-side code if that is
not necessary.
Many TIA, peter
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Gearon | 2003-03-05 16:12:33 | Re: nextval::text |
Previous Message | Tom Lane | 2003-03-05 16:00:08 | Re: pg_ctl -m fast failing? |