Re: Newbie basic SQL question.

RufusA_at_rocketmail.com (Rufus A) wrote in message news:<6be277be.0309060610.635cf339_at_posting.google.com>...
> All I need to find is the employee's *current* job title.
>
> Whatever I try seems to generate an error, either grouping not allowed
> in that context, or missing right parenthesis. My latest attempt is:
>
> Select JobTitle from T_Jobs where emplid="12345" and effdt=(Select
> max(A2.effdt) from T_Jobs A2 where emplid = A2.emplid and A2.effdt <=
> sysdate)
>
> Where JobTitle is the column I want, emplid is the employee id, and
> effdt is the date that record is effective from.

You are close. The following should work for you: Find the jobs where start date is the same as or prior to the current date, and order and get the most recent record:

select JobTitle from T_Jobs
where emplid = '12345' and

            effdt <= sysdate and
            rownum <= 1

> Could someone please point me in the right direction.
>
> Thanks - Rufus.
Received on Sat Sep 06 2003 - 21:36:29 CEST