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 <lists@boutiquenumerique.com>
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: <op.suffhmy3th1vuj@localhost>
In-Reply-To: <20050721103304.2209252B23@svr1.postgresql.org>
User-Agent: Opera M2/8.0 (Linux, build 1095)


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