Retrieving the top row of data I have very limited SQL knowledge and need to report on the top most current row of data within a table.  The table holds Effective Date, Information and a Renewal Date.  

select DATA_KEY_ID, 
TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE", DATA_KEY_REF, DATA_KEY_ID  
from TABLENAME 
where SCREEN_REF = 'TEST' and FIELD_REF = (select REF from REF_TABLE where SCREEN_REF = 'TEST' and ID = 'EFFDTE')


This brings the correct effective date, however it also brings out all the rows that have been entered.  I need to only bring out the top row.

Sorry if this is a bit random.
Have you tried something like this (syntax is for SQL Server):

SELECT TOP(1) 
    DATA_KEY_ID
    , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
    , DATA_KEY_REF
    , DATA_KEY_ID  
FROM TableName 
WHERE SCREEN_REF = 'TEST' 
    and FIELD_REF = (
            select REF 
            from REF_TABLE 
            where SCREEN_REF = 'TEST' 
                    and ID = 'EFFDTE'
            )


The TOP(1) clause just after the SELECT tells the database engine to return a single row.  In order for this to return the same row each time it is executed, you should also include an ORDER BY clause at the end such as:

ORDER BY DATA_KEY_ID;


If you are using MySQL, the syntax would be something like:

SELECT  
    DATA_KEY_ID
    , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
    , DATA_KEY_REF
    , DATA_KEY_ID  
FROM TableName 
WHERE SCREEN_REF = 'TEST' 
    and FIELD_REF = (
            select REF 
            from REF_TABLE 
            where SCREEN_REF = 'TEST' 
                    and ID = 'EFFDTE'
            )
ORDER BY DATA_KEY_ID
LIMIT 0,1;


If you specify the type of RDBMS you are using, that would help identify the actual syntax you should use.

For Oracle, which seems like the platform you may be using, you need to add a reference to ROWNUM to the WHERE clause, like:

SELECT *
FROM
  ( SELECT  
        DATA_KEY_ID
        , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
        , DATA_KEY_REF
        , DATA_KEY_ID  
    FROM TableName 
    WHERE SCREEN_REF = 'TEST' 
        and FIELD_REF = (
                select REF 
                from REF_TABLE 
                where SCREEN_REF = 'TEST' 
                        and ID = 'EFFDTE'
                )
    ORDER BY DATA_KEY_ID
  ) x
WHERE ROWNUM = 1;