Difficult query

Started by Peter Albererabout 23 years ago3 messagesgeneral
Jump to latest
#1Peter Alberer
h9351252@obelix.wu-wien.ac.at

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

#2Manfred Koizar
mkoi-pg@aon.at
In reply to: Peter Alberer (#1)
Re: Difficult query

On Wed, 5 Mar 2003 17:02:42 +0100, "Peter Alberer"
<h9351252@obelix.wu-wien.ac.at> wrote:

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.

Peter,

DISTINCT ON is your friend. I never tried to use it together with
GROUP BY; at least you can use your original query as a sub-select
and wrap the DISTINCT ON query around it.

SELECT DISTINCT ON (area) area, user_id, total, succ
FROM (SELECT ...) AS t
ORDER BY area, succ DESC, total DESC;

Servus
Manfred

#3Peter Alberer
h9351252@obelix.wu-wien.ac.at
In reply to: Manfred Koizar (#2)
Re: Difficult query

Thanks a lot Manfred that is exactly what is was searching for!

I tried a few things with distinct and max in a similar select -
sub-select combination but did not know about "distinct on".

peter

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] Im Auftrag von Manfred Koizar
Gesendet: Donnerstag, 06. März 2003 11:39
An: Peter Alberer
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Difficult query

On Wed, 5 Mar 2003 17:02:42 +0100, "Peter Alberer"
<h9351252@obelix.wu-wien.ac.at> wrote:

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.

Peter,

DISTINCT ON is your friend. I never tried to use it together with
GROUP BY; at least you can use your original query as a sub-select
and wrap the DISTINCT ON query around it.

SELECT DISTINCT ON (area) area, user_id, total, succ
FROM (SELECT ...) AS t
ORDER BY area, succ DESC, total DESC;

Servus
Manfred

---------------------------(end of

broadcast)---------------------------

Show quoted text

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html