Joseph Shraibman <[EMAIL PROTECTED]> writes:
> I'm not clear how this helps. I want to get the last entry of ml.
> The distinct on means I won't get duplicate entries with the same key
> values, but what specifies that I'm getting the last ml value, or even
> one ml value at all?
The DISTINCT en
I'm not clear how this helps. I want to get the last entry of ml. The distinct on
means
I won't get duplicate entries with the same key values, but what specifies that I'm
getting the last ml value, or even one ml value at all?
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Basically there is the utable, which has two keys in it. I want to
> select some rows from the utable. Some columns come from the dtable,
> which has a key that matches to one of the utable keys, and I want to
> select the last value from the mltabl
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Thanks for the response. Chris Ruprecht and I got into an off-list
> conversation and basically determined that the problem is 90% likely to
> be disk access time.
Hmm. Can you cut the amount of disk access by reducing the number of
updates that you d
Tom,
> I kinda doubt it. More likely sources of trouble involve the
> optimizer
> picking bad plans because it can't see the exact values being used in
> queries. Can you show us the details of the function?
Thanks for the response. Chris Ruprecht and I got into an off-list
conversation and b
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> I can't help but feel that, because functions wrap everything in a
> transaction, some sort of tinkering with the xlog settings/performance
> is called for ...
I kinda doubt it. More likely sources of trouble involve the optimizer
picking bad plans bec
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Bascially the point of the subselect is to get the field value where
> serial is hightest and the two keys match.
If you don't mind nonstandard SQL, perhaps something involving SELECT
DISTINCT ON would solve your problem (look at the weather-report e
Josh Berkus wrote:
> Joseph,
>
>
>>Actually I do it all the time, in the select part.
>>
>
> Hmm. Frankly, I didn't know that Subselects in the field list were
> supported, so this is a new one on me.
>
>
>
>>Well that is the problem. In my subselect I only want the latest
>>value of ml.
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> From the postgres docs:
> A sub-SELECT can appear in the FROM clause. This acts as though its output
>were
> created as a temporary table for the duration of this single SELECT command. Note
>that the
> sub-SELECT must be surrounded by par
Josh Berkus wrote:
> Joseph,
>
>
>>select d.field1, d.field2, u.field1, u.field2, (select ml.field from
>>mltable where ml.key1
>>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from
>>utable u, dtable d,
>>where u.key1 = d.key order by d.somefield limit 25 offset ???;
>>
>
>
Actually I think I discovered part of the problem. My subquery did:
ORDER BY serial DESC LIMIT 1
where serial happened to be the primary key. The explain showed that it was doing an
index search on this serial. So I did the order by on a date field that doesn't have
an
index, so the query
Except I want ml.field, which is a data field, not a key. So I can't group by it.
Bascially the point of the subselect is to get the field value where serial is
hightest
and the two keys match.
Josh Berkus wrote:
> Joseph,
>
>
>>select d.field1, d.field2, u.field1, u.field2, (select ml.fiel
Joseph,
> select d.field1, d.field2, u.field1, u.field2, (select ml.field from
> mltable where ml.key1
> = u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from
> utable u, dtable d,
> where u.key1 = d.key order by d.somefield limit 25 offset ???;
>From the look of it, you want to
On Mon, 27 Aug 2001, Joseph Shraibman wrote:
> Stephan Szabo wrote:
> > I think you'd want to move the entire query excepting the lastml where
> > condition into a single subselect in the outer from with the lastml
> > condition on the outside:
> > select * from () as blah where
> > lastml=2;
Joseph,
> Actually I do it all the time, in the select part.
Hmm. Frankly, I didn't know that Subselects in the field list were
supported, so this is a new one on me.
> Well that is the problem. In my subselect I only want the latest
> value of ml.field, which
> I get my ordering my ml.keyf
Stephan Szabo wrote:
>
>
> I think you'd want to move the entire query excepting the lastml where
> condition into a single subselect in the outer from with the lastml
> condition on the outside:
> select * from () as blah where
> lastml=2;
>
> However, I don't think this changes the comput
Josh Berkus wrote:
> Joseph,
>
> Please take a look at my example again:
>
>
>>>SELECT tbla.a, tbla.b, total_b
>>>FROM tbla,
>>> (SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
>>>WHERE tbla.b = b_tot.b
>>>
>>>
>
>>OK when I tried to convert the query:
>>select u.field1, ... , d
On Mon, 27 Aug 2001, Joseph Shraibman wrote:
> OK my query was like:
> select u.field1, ... , d.field1, ... (select ml.field from ml WHERE ml.key = u.key
>order
> by ml.keyfield desc limit 1) as lastml FROM utable u, dtable d, WHERE u.key = d.key
>and
> ... and (sublectect again) = 2;
>
> OK
Joseph,
Please take a look at my example again:
> > SELECT tbla.a, tbla.b, total_b
> > FROM tbla,
> >(SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
> > WHERE tbla.b = b_tot.b
> >
> OK when I tried to convert the query:
> select u.field1, ... , d.field1, ..., mll.lastml FROM uta
Joseph,
> How do you do a subselect in the from clause?
Assuming that you are using 7.1.0 or higher:
SELECT tbla.a, tbla.b, total_b
FROM tbla,
(SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
WHERE tbla.b = b_tot.b
If you're using 7.0 still, you're S.O.L. Time to upgrade ;-)
-Jo
Josh Berkus wrote:
> Joseph,
>
>
>>How do you do a subselect in the from clause?
>>
>
> Assuming that you are using 7.1.0 or higher:
>
> SELECT tbla.a, tbla.b, total_b
> FROM tbla,
>(SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
> WHERE tbla.b = b_tot.b
>
OK my query was li
How do you do a subselect in the from clause?
Tom Lane wrote:
> Joseph Shraibman <[EMAIL PROTECTED]> writes:
>
>>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
>>ERROR: Attribute 'dsum' not found
>>
>
>>Why can we GROUP BY on an alias but not do a WHERE on an alias?
>
Folks,
The PostgreSQL Date/Time/Interval FAQ has been posted to techdocs:
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php
Any expansions of the FAQ are welcome, particularly:
1. An explanation of TIMEZONE manipulation.
2. Replacement DATEADD and DATEDIFF functions
Folks,
I have this function that adds 100-900 rows to a table and then unpdates
them 12 times using data pulled from all over the database. I've
increased the pgsql buffer, the sort memory, and wal_files significantly
(2048, 1024 and 16) as well as adding a few relevant indexes.
However, this f
24 matches
Mail list logo
