Posted by Hilarion on 09/27/05 15:21

listerofsmeg01@hotmail.com wrote:
> OK, I have tried reading the manual on this one, but I cannot work it
> out. Probly easy for a SQL veteran...
>
> I need to get the position (ie 3rd from top - 3) of a particular record
> FROM AN ORDERED RESULT SET.
>
> In other words, I need to do something like:
> SELECT "position or whatever the magic word is" from (SELECT * FROM
> MyTable ORDER BY Date) where Name = "someone";
>
> So given "someone", I want to find out how recent their entry is (eg
> 3rd most recent).


In MySQL you could use LIMIT clause like this:

SELECT *
FROM mytable
WHERE name = 'someone'
ORDER BY mydate
LIMIT 2, 1


which will return third "someone" (in "myate" order). (Do not use "date"
as column name because it is a reserverd keyword - name of type - in
many SQL dialects and may cause problems.)
"LIMIT 2, 1" means that two first results are skipped and one is returned.

If you do not use MySQL, then you'll have to check if and how does your
RDBMS SQL supports resultset limiting. The popular clause name for this
is "TOP" (used right after SELECT keyword). In Oracle 8i you could use
ROWNUM pseudocolumn with subquery to get the same result.
If your RDBMS does not support resultset limiting, then you can always
do it on PHP side - retrieve first three records and use only data
from the third one.


Hilarion

[Back to original message]