Need some with query

Hi, I need some help formulating a query. Below is an example records.
What I am trying to do is to come up with a query that will tell me which
record has a position_startdate that is less than position_enddate from the
record above.
ID EMP_ID POSITION POSITION_STARTDATE POSITION_ENDDATE
-- -- -- -- --
--
18 18 SALES 1/21/03
6/5/04
25 18 SALES MGR 6/5/04
12/28/04
31 18 DEPT SUP 10/18/04
8/8/05
45 18 STORE MGR 8/8/05
null
In this example, the query should pickup ID = 31 since 10/18/04 is less than
the previous postion_enddate which is 12/28/04.
Also, I am running this query with tons or employees.
I am currently working on this and thought that I might post it and get a
quick response back.
Thanks,
JB..Rick Shaw wrote:

> Below is an example
> records. What I am trying to do is to come up with a query that will
> tell me which record has a position_startdate that is less than
> position_enddate from the record above.
You should provide more information to get a better answer but I think
this should do it, assuming that when you say 'record above' you mean a
record with a lower ID.
Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
Desc)
HTH,
Stijn Verrept.|||Stijn Verrept wrote:

> Rick Shaw wrote:
>
> You should provide more information to get a better answer but I think
> this should do it, assuming that when you say 'record above' you mean
> a record with a lower ID.
> Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
> Desc)
Maybe
Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
T2.Position_EndDate from Table T2 where T2.ID < T1.ID and T1.Emp_ID =
T2.Emp_ID order by T2.ID
Desc)
is better, depends if you need to compare it with the same Emp_ID or not
Kind regards,
Stijn Verrept.|||Thanks Stijn. It worked great.
Thanks again for the hand.
Rick..
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:v7KdnRH9lLMwlRjenZ2dnUVZ8qudnZ2d@.sc
arlet.biz...
> Stijn Verrept wrote:
>
> Maybe
> Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> T2.Position_EndDate from Table T2 where T2.ID < T1.ID and T1.Emp_ID =
> T2.Emp_ID order by T2.ID
> Desc)
> is better, depends if you need to compare it with the same Emp_ID or not
> --
> Kind regards,
> Stijn Verrept.