From ricky@connexiasolutions.com Thu Jul 21 10:36:02 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4608D52B3A for ; Thu, 21 Jul 2005 07:36:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26988-06 for ; Thu, 21 Jul 2005 10:35:53 +0000 (GMT) Received: from griffin.idwebhost.com (unknown [202.67.9.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2209252B23 for ; Thu, 21 Jul 2005 07:33:04 -0300 (ADT) Received: from [222.124.53.34] (helo=nebulla) by griffin.idwebhost.com with esmtpa (Exim 4.44) id 1DvYLO-0007Yd-Jt for pgsql-sql@postgresql.org; Thu, 21 Jul 2005 17:31:59 +0700 Reply-To: From: "Ricky Sutanto" To: Subject: Counting Row Date: Thu, 21 Jul 2005 17:33:32 +0700 Organization: PT. Connexia Makmur Jaya MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C58E1A.51FC4BB0" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcWN36RwvfiTKp6GSyG9O8d5HarUCQ== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - griffin.idwebhost.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - connexiasolutions.com X-Source: X-Source-Args: X-Source-Dir: Message-Id: <20050721103304.2209252B23@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.705 tagged_above=0 required=5 tests=HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200507/212 X-Sequence-Number: 22190 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C58E1A.51FC4BB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi postgre people. Can anybody help to fix performance of my query? Let say, I have n number of queue ticket. (Which I use SELECT). Is there faster method to know where is my ticket number in queue? I use (while in PHP script ) and count one by one until my ticket number match with row field . $sql="select * from [MYTABLE] where [CONDITION] order by [FIELDORDER]; $rs=&$ctclink->Execute($sql); $ctr=1; $pos=0; while (!$rs->EOF) { if ($rs->fields[0]==$recid) { $pos=$ctr; break; } else { $ctr++; $rs->MoveNext(); } } print $pos is there any method to do that with simple?? Thanks, ------=_NextPart_000_0001_01C58E1A.51FC4BB0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi postgre people. Can anybody help to fix = performance of my query?

Let say, I have n number of queue ticket. (Which I use SELECT).

Is there faster method to know where is my ticket = number in queue?

I use (while in PHP script ) and count one by = one until my ticket number match with row field .

$sql=3D"select * from [MYTABLE] where = [CONDITION] order by [FIELDORDER];

$rs=3D&$ctclink->Execute($sql);

$ctr=3D1;

$pos=3D0;

while (!$rs->EOF) {

if ($rs->fields[0]=3D=3D$recid) {

= $pos=3D$ctr;

= break;

} else = {

= $ctr++;

= $rs->MoveNext(); }

}

print $pos

is there any method to do that with = simple??

Thanks,

------=_NextPart_000_0001_01C58E1A.51FC4BB0-- From ricky@connexiasolutions.com Fri Jul 22 03:12:08 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AF78D5288E for ; Fri, 22 Jul 2005 00:12:08 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35578-03 for ; Fri, 22 Jul 2005 03:11:58 +0000 (GMT) Received: from griffin.idwebhost.com (unknown [202.67.9.242]) by svr1.postgresql.org (Postfix) with ESMTP id CBBCD5284E for ; Fri, 22 Jul 2005 00:11:54 -0300 (ADT) Received: from [222.124.53.163] (helo=nebulla) by griffin.idwebhost.com with esmtpa (Exim 4.44) id 1Dvnwn-0008UY-7H for pgsql-sql@postgresql.org; Fri, 22 Jul 2005 10:11:39 +0700 Reply-To: From: "Ricky Sutanto" To: Subject: Counting Row Date: Fri, 22 Jul 2005 10:13:12 +0700 Organization: PT. Connexia Makmur Jaya MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0009_01C58EA5.F8B078B0" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcWN36RwvfiTKp6GSyG9O8d5HarUCQAi5rOQ X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - griffin.idwebhost.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - connexiasolutions.com X-Source: X-Source-Args: X-Source-Dir: Message-Id: <20050722031154.CBBCD5284E@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.705 tagged_above=0 required=5 tests=HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200507/210 X-Sequence-Number: 22188 This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C58EA5.F8B078B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi postgre people. Can anybody help to fix performance of my query? Let say, I have n number of queue ticket. (Which I use SELECT). Is there faster method to know where is my ticket number in queue? I use (while in PHP script ) and count one by one until my ticket number match with row field . $sql="select * from [MYTABLE] where [CONDITION] order by [FIELDORDER]; $rs=&$ctclink->Execute($sql); $ctr=1; $pos=0; while (!$rs->EOF) { if ($rs->fields[0]==$recid) { $pos=$ctr; break; } else { $ctr++; $rs->MoveNext(); } } print $pos is there any method to do that with simple?? Thanks, ------=_NextPart_000_0009_01C58EA5.F8B078B0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi postgre people. Can anybody help to fix = performance of my query?

Let say, I have n number of queue ticket. (Which I use SELECT).

Is there faster method to know where is my ticket = number in queue?

I use (while in PHP script ) and count one by = one until my ticket number match with row field .

$sql=3D"select * from [MYTABLE] where = [CONDITION] order by [FIELDORDER];

$rs=3D&$ctclink->Execute($sql);

$ctr=3D1;

$pos=3D0;

while (!$rs->EOF) {

if ($rs->fields[0]=3D=3D$recid) {

= $pos=3D$ctr;

= break;

} else = {

= $ctr++;

= $rs->MoveNext(); }

}

print $pos

is there any method to do that with = simple??

Thanks,

------=_NextPart_000_0009_01C58EA5.F8B078B0-- From lists@boutiquenumerique.com Sun Jul 24 16:45:16 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9FE2B529B1 for ; Sun, 24 Jul 2005 13:45:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13596-10 for ; Sun, 24 Jul 2005 16:45:15 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id E395D52994 for ; Sun, 24 Jul 2005 13:45:13 -0300 (ADT) Received: (qmail 1537 invoked from network); 24 Jul 2005 18:45:14 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 24 Jul 2005 18:45:14 +0200 Date: Sun, 24 Jul 2005 18:45:12 +0200 To: ricky@connexiasolutions.com, pgsql-sql@postgresql.org Subject: Re: Counting Row References: <20050721103304.2209252B23@svr1.postgresql.org> From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <20050721103304.2209252B23@svr1.postgresql.org> User-Agent: Opera M2/8.0 (Linux, build 1095) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/215 X-Sequence-Number: 22193 I'd suggest : - first getting the 'order fields' value for the ticket you want : SELECT field_order FROM mytable WHERE condition AND identifier=the_one_you're_looking_for ORDER BY field_order DESC LIMIT 1 - then counting all the tickets up to this order : SELECT count(*) FROM mytable WHERE condition AND field_order <= (SELECT field_order FROM mytable WHERE condition AND identifier=the_one_you're_looking_for ORDER BY field_order DESC LIMIT 1) You could also use a plpgsql function to reimplement your PHP thing, which should be faster than doing it in PHP, but probably slower than the example above. # But there is a simpler solution. How do you model the position of a ticket in the queue ? What is the 'order field' you mention ? If your table has a SERIAL PRIMARY KEY, it's monotonous, so you can use this without further worries. table tickets_queues (id SERIAL PRIMARY KEY, queue_id, ticket_id, UNIQUE( queue_id, ticket_id) ) to get the position of a ticket (by its ticket_id) in a queue : SELECT count(*) FROM tickets_queue WHERE queue_id=# AND id <= (SELECT id FROM tickets_queue WHERE ticket_id=# AND queue_id=#) From deruvo@gmail.com Sun Jul 24 22:07:12 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 579E852AA3 for ; Sun, 24 Jul 2005 19:07:12 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87980-06 for ; Sun, 24 Jul 2005 22:07:08 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.205]) by svr1.postgresql.org (Postfix) with ESMTP id CCDA652A51 for ; Sun, 24 Jul 2005 19:07:06 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 69so894369wri for ; Sun, 24 Jul 2005 15:07:09 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WdcQhkxEVjW4Jcx0F2gyFrMi91b+hxExrfhmXfloky7dZXtVHzRJe9rcSj6JY2hCFEdCMxh8g5uEd1Ob5gQWd669LY1XHUChdUr5sv51a+0GJFwB3NV46Ok6ekrf9jP3zaTjPwEFaoDc2Yo5QJwKHeJIt04dVviKAoV1UTLIj0I= Received: by 10.54.34.7 with SMTP id h7mr1957027wrh; Sun, 24 Jul 2005 15:07:09 -0700 (PDT) Received: by 10.54.135.1 with HTTP; Sun, 24 Jul 2005 15:07:09 -0700 (PDT) Message-ID: Date: Sun, 24 Jul 2005 18:07:09 -0400 From: Pascual De Ruvo Reply-To: Pascual De Ruvo To: ricky@connexiasolutions.com Subject: Re: Counting Row Cc: pgsql-sql@postgresql.org In-Reply-To: <20050722031154.CBBCD5284E@svr1.postgresql.org> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1373_22799982.1122242829304" References: <20050722031154.CBBCD5284E@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.257 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/217 X-Sequence-Number: 22195 ------=_Part_1373_22799982.1122242829304 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I understand you need to count of tickets smaller than $recid given=20 [CONDITION], in that case: $sql=3D"select count(*) from [MYTABLE] where [CONDITION] and ticketnumber <= =3D"=20 .$recid ; On 7/21/05, Ricky Sutanto wrote: >=20 > Hi postgre people. Can anybody help to fix performance of my query? >=20 > Let say, I have *n* number of queue ticket. (Which I use SELECT). >=20 > Is there faster method to know where is my ticket number in queue? >=20 > I use (while in PHP script ) and count one by one until my ticket number= =20 > match with row field . >=20 > $sql=3D"select * from [MYTABLE] where [CONDITION] order by [FIELDORDER]; >=20 > $rs=3D&$ctclink->Execute($sql); >=20 > $ctr=3D1; >=20 > $pos=3D0; >=20 > while (!$rs->EOF) { >=20 > if ($rs->fields[0]=3D=3D$recid) { >=20 > $pos=3D$ctr; >=20 > break; >=20 > } else { >=20 > $ctr++; >=20 > $rs->MoveNext(); } >=20 > } >=20 > print $pos >=20 > is there any method to do that with simple?? >=20 > *Thanks,* >=20 > ------=_Part_1373_22799982.1122242829304 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I understand you need to count of tickets smaller than $recid given [CONDIT= ION], in that case:

$sql=3D"select count(*) from [MYTABLE] where [CONDITION] and= ticketnumber <=3D" .= $recid ;

On 7/21/05, Ricky Sutanto <ri= cky@connexiasolutions.com> wrote:

Hi postgre people. Can anybody help to fix performance of my query?

Let say, I have n= number of queue ticket. (Which I use SELECT).

Is there faster method to know where is my ticket number in queue?

I use (while in PHP script ) and count one by one until my ticket number match with row field .

$sql=3D"select * from [MYTABLE] where [CONDITION] order by [FIELDORDER];

$rs=3D&$ctclink->Execute($sql);

$ctr=3D1;

$pos=3D0;



   while (!$rs->EOF) {

      if ($rs->fields[0]=3D=3D$recid) {

        $pos=3D$ctr;=

        break;

      } else {

      $ctr++;

      $rs->MoveNext(); }

    }

   print $pos

is there any method to do that with simple??

Thanks,

------=_Part_1373_22799982.1122242829304--