From: | Guillaume Perréal <perreal(at)lyon(dot)cemagref(dot)fr> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Eric Jain <jain(at)gmx(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Combining two SELECTs |
Date: | 2000-07-05 07:35:44 |
Message-ID: | 3962E550.B685F556@lyon.cemagref.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane wrote:
>
> "Eric Jain" <jain(at)gmx(dot)net> writes:
> > Any ideas how the following two statements could be combined into a
> > single one?
>
> > SELECT DISTINCT host, url, id
> > INTO TEMP
> > FROM log
> > WHERE
> > host IN (SELECT host FROM robots)
> > AND status IN (200, 304);
>
> > SELECT host, COUNT(*) AS hits
> > FROM TEMP
> > GROUP BY host
> > ORDER BY hits DESC;
>
> Offhand I do not think you can do this in one "simple" SQL query,
> because the SQL query semantics require that GROUP BY grouping occurs
> before DISTINCT processing, whereas you want the other order.
>
>
> For now, the temp table seems like a good workaround.
>
And splitting some complex queries in simpler ones (using temp tables) can
increase performance, depending on the query.
Regards,
Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64
From | Date | Subject | |
---|---|---|---|
Next Message | Gilles DAROLD | 2000-07-05 08:57:28 | Re: responses to licensing discussion |
Previous Message | Tom Lane | 2000-07-05 07:13:50 | Re: Combining two SELECTs |