Обсуждение: Counting Row
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,
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=#)