Counting Row

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Counting Row
4+ messages / 3 participants
[nested] [flat]

* Counting Row
@ 2005-07-21 10:33 Ricky Sutanto <ricky@connexiasolutions.com>
  2005-07-24 16:45 ` Re: Counting Row PFC <lists@boutiquenumerique.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Ricky Sutanto @ 2005-07-21 10:33 UTC (permalink / raw)
  To: pgsql-sql

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,

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Counting Row
  2005-07-21 10:33 Counting Row Ricky Sutanto <ricky@connexiasolutions.com>
@ 2005-07-24 16:45 ` PFC <lists@boutiquenumerique.com>
  0 siblings, 0 replies; 4+ messages in thread

From: PFC @ 2005-07-24 16:45 UTC (permalink / raw)
  To: ricky@connexiasolutions.com; pgsql-sql


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=#)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Counting Row
@ 2005-07-22 03:13 Ricky Sutanto <ricky@connexiasolutions.com>
  2005-07-24 22:07 ` Re: Counting Row Pascual De Ruvo <deruvo@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Ricky Sutanto @ 2005-07-22 03:13 UTC (permalink / raw)
  To: pgsql-sql

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,

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Counting Row
  2005-07-22 03:13 Counting Row Ricky Sutanto <ricky@connexiasolutions.com>
@ 2005-07-24 22:07 ` Pascual De Ruvo <deruvo@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Pascual De Ruvo @ 2005-07-24 22:07 UTC (permalink / raw)
  To: ricky@connexiasolutions.com; +Cc: pgsql-sql

I understand you need to count of tickets smaller than $recid given 
[CONDITION], in that case:

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


On 7/21/05, Ricky Sutanto <ricky@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="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,*
> 
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2005-07-24 22:07 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-21 10:33 Counting Row Ricky Sutanto <ricky@connexiasolutions.com>
2005-07-24 16:45 ` PFC <lists@boutiquenumerique.com>
2005-07-22 03:13 Counting Row Ricky Sutanto <ricky@connexiasolutions.com>
2005-07-24 22:07 ` Pascual De Ruvo <deruvo@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox