(SELECT a2.id, a2.ts as ts_p, min(b2.ts) AS ts_c
FROM tbl AS a2, tbl AS b2
WHERE a2.id = b2.id AND a2.ts + interval'5 minute' <= b2.ts
GROUP BY a2.id, a2.ts
UNION ALL
SELECT a3.id, a3.ts, null
FROM tbl AS a3
) AS t2 ON t1.id = t2.id AN
On Fri, 3 Jun 2011 15:52:53 -0400
lists-pg...@useunix.net wrote:
I also think you might want to use WITH RECURSIVE clause.
This SQL searches the case of an interval of 5 minutes or more,
and sets a relationship between a parent to its child.
CREATE TABLE tbl(id integer, ts time) ;
INSERT INTO
>
> Original Message
> Subject: Re:[SQL] Subquery problems
> From: Masaru Sugawara <[EMAIL PROTECTED]>
> To: Ranieri Mazili <[EMAIL PROTECTED]>
> Date: 21/6/2007 13:25
> > On Tue, 19 Jun 2007 09:17:22 -0300
> > Ranieri Mazili <[EMAIL PROTECTED]&g
On Tue, 19 Jun 2007 09:17:22 -0300
Ranieri Mazili <[EMAIL PROTECTED]> wrote:
Hi,
This reply is not accurate, but I think there are helpful hints.
--
Masaru Sugawara
select
C.id_production_area,
B.id_machine_type,
A.h_month as month,
max(A.n) as div_mes,
cas
On Thu, 07 Nov 2002 09:57:27 +0100
Christoph Haller <[EMAIL PROTECTED]> wrote:
> > ORDER BY proj.project_id ;
>
> What about simply replacing ORDER BY proj.project_id ; by
> GROUP BY project_id, marketing_name ;
You're right. Thanks a lot.
'GGH') AS proj,
lots
WHERE
dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND NOT EXISTS
(SELECT 1 FROM menu_group_projects
WHERE men
ts.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND NOT EXISTS
(SELECT 1 FROM menu_group_projects
WHERE menu_code = 'WA'
AND division_id = proj.division_id
AND project_id = proj.project_id
AND status = 'I')
ORDER BY proj.project_id
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
, purchase_date DESC
) AS t4
LIMIT ALL
) AS t5
WHERE t3.item_no = t5.item_no
AND t3.supplier = t5.supplier
AND t5.i - t3.n + 1 <= 3
;
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 3: if posting/reading through
) AS t2
GROUP BY t2.a_id, t2.d_id, t2.n
HAVING COUNT(*) = t2.n
) AS t3,
a,
d
WHERE a.id = t3.a_id
AND d.id = t3.d_id
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 3: if posting/reading through Usenet,
; >
> >
> > SELECT a.name, d.name
> > FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id
> > FROM b, c
> >WHERE b.c_id = c.b_id
> > GROUP BY b.c_id, c.d_id
> > ) AS t,
> > a,
> > d
> > WHERE a.id = t.a_
_code from
>status_log where schedule_id =schedule_id order by complete_time desc ;
PostgreSQL doesn't have a TOP keyword. You'll need to use a LIMIT keyword.
See docs.
http://www.postgresql.org/idocs/index.php?sql-select.html
Regards,
Masaru Sugawara
---
d
FROM b, c
WHERE b.c_id = c.b_id
GROUP BY b.c_id, c.d_id
) AS t,
a,
d
WHERE a.id = t.a_id
AND d.id = t.d_id
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please
indices like the following?
If not so, could you execute EXPLAIN ANALYZE after creating the indices.
create index idx_lineitem_orderkey on lineitem(orderkey);
create index idx_orders_orderkey on orders(orderkey);
create index idx_orders_custkey on orders(custkey);
cre
rkey)
WHERE
orders.orderkey IS NOT NULL
GROUP BY
orders.orderkey
HAVING
SUM(lineitem.quantity) > 300
) AS t1 LEFT OUTER JOIN
orders AS t2 USING(orderkey)
ORDER BY t2.custkey
the following query take in your situation,
and how many rows does it retrieve ?
EXPLAIN ANALYZE
SELECT
lineitem.orderkey
FROM
lineitem
GROUP BY
lineitem.orderkey
HAVING
SUM(lineitem.quantity) > 300;
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
average
FROM tracks, albums
WHERE tracks.DISCID = albums.DISCID
GROUP BY albums.DISCID, albums.LENGTH
) AS mx
)
) AS a1
Regards
which is much faster.
You are right. And this type of optimising are not yet implemented.
Tom said it in the prior discussions.
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropr
AND
o.sid = t.sid AND
o.entrancetime = MAX(t.entrancetime)
)
;
By the way, a mail server have been downed ?
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
UERY PLAN might decrease. However, this trial may be a
vain effort. I can't expect the result of the QUERY PLAN. :-)
The indices: "stories_source",
"stories_unique_story",
and "stories_urn_key"
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
select (count(*)+1)/2 from ages)
and a1.rank <= (select count(*)/2+1 from ages)
)
;
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
and u1.c = u0.c
)
;
a | b | c | d
---+-++-
2 | BAR | Z2 | 101
2 | FOO | A1 | 101
3 | FOO | A1 | 102
4 | BAR | Z2 | 99
7 | BAR | ZY | 105
7 | FOO | AB | 105
(6 rows)
Have a nice weekend!
--
Masaru Sugawara
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Peter Schmidt wrote:
>I'm having trouble finding the best solution for the following plpgsql
>function.
>The function does the following:
Hi, I post in this mailing list for the first time. :-)
I think your pl/pgsql function runs properly, but there
is likely to exist another SQL pattern. In
22 matches
Mail list logo
