Retrieving the top row of data

Question

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.

Answer 1

The best syntax I have found is:

select * where  ( Conditions) and rownum = 1;

You can use order by with the rownum psudocolumn to get only the top row in the ordered query.

Select * from (
select * from ( Conditons ) order by column_1
) where rownum = 1;

Answer 2

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;

Answer 3

First, you need to be very mindful of what you mean by "top row". In a relational table, rows are like balls in a basket. There is no inherent 'first' or 'top' until you specifically and explicitly include an appropriate ORDER BY in your query. Also be careful of the use of rownum. In oracle, the ROWNUM is applied to the rows as they are chosen for the result set. It does not mean anything about the rows as they exist on the table.