From: PFC <lists@boutiquenumerique.com>
To: ricky@connexiasolutions.com
To: pgsql-sql@postgresql.org
Subject: Re: Counting Row
Date: Sun, 24 Jul 2005 18:45:12 +0200
Message-ID: <op.suffhmy3th1vuj@localhost> (raw)
In-Reply-To: <20050721103304.2209252B23@svr1.postgresql.org>
References: <20050721103304.2209252B23@svr1.postgresql.org>
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=#)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@boutiquenumerique.com, ricky@connexiasolutions.com
Subject: Re: Counting Row
In-Reply-To: <op.suffhmy3th1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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