"SELECT TOP" in Oracle SQL

[mrfritz44]

I need to select a record that has the latest date in a group of records. In MS SQL, I would say:

select * from tableA where effdt = (select top 1 effdt from tableB order by effdt desc)

How would I accomplish this in Oracle SQL?

Thank you,

Fred

 

[lewisp]

SELECT *
FROM   tableA
WHERE  effdt = (SELECT MAX(effdt)
                FROM   tableB);

 

[mrfritz44]

Thank you very much! OK, now for one I haven't needed yet, but likely will. How would select the top 5, 10, or 100?

 

[lewisp]

SELECT *
FROM   tableA
WHERE  effdt IN ( SELECT effdt
                  FROM (
                    SELECT effdt
                    FROM   tableB
                    ORDER BY effdt desc )
                  WHERE  rownum < 11 );

 

[nagornyi]

SELECT * FROM
(SELECT * FROM tableA ORDER BY effdt DESC)
WHERE ROWNUM<=10