Posted by Stefan Rybacki on 09/27/05 15:17

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).

Its easy in your special case. Just count the entries with newer or older dates and you
know the position.

for older:

SELECT count(*) FROM table WHERE date<(SELECT date FROM table WHERE name="someone")

for newer:

SELECT count(*) FROM table WHERE date>(SELECT date FROM table WHERE name="someone")


Regards
Stefan

>
> Thanks,
> Lister
>

[Back to original message]