On Tue, Jan 23, 2001 at 05:39:47PM +0100, Sander Pilon wrote:
> Okay, here's one for the guru's out there :)
>
> I have a list of entries with unique id numbers X, and a set of sort methods
> (S1 ... S<y>).
>
> Now, if I want to get an entry at position P (0...<z>) in the list of
> entries ordered by method S1 then I'd
> make the following query:
>
> SELECT X FROM table WHERE .... ORDER BY S1 LIMIT P,1
>
> But now I want the inverse - given an id X and a sort method, I want the
> position.
>
> something like: SELECT POSITION(X) FROM table WHERE ..... ORDER BY S1
>
> Is there a way to do this?
You could try something like..
SELECT COUNT(X) FROM table
WHERE .... AND X < (yourX)
ORDER BY S1
where X is the name of the field (literally), and yourX is the value
you're interested in. After that, just add 1.
NOTE: This does not guarantee that yourX actually exists in the table;
if it doesn't, this will happily return the position yourX WOULD HAVE BEEN
at, had it existed.
To find out if it exists, you'll have to do a separate query.
G'luck,
Peter
--
The rest of this sentence is written in Thailand, on
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
