0

The TableC and TableB have multiple records, I want select multiple records from TableB and one record from TableC which is working in SQL Server. But actual requirement in Oracle database where it's not working. Please help to convert below SQL Server query to Oracle.

Select 
     Data-A,
     Data-B,
    (Select Top 1 DATA-C from
     TableC Where TableC.SourcePtr=Tabbleb.Rkey order by Step Desc) as Data-C
From TableA,
     TableB
Where TableA.Source_PTR =TableB.Rkey
Karthik
  • 3
  • 1
  • "SQL query"? I assume you mean MS SQL Server query. – jarlh Aug 23 '21 at 07:47
  • Yea it's MS SQL – Karthik Aug 23 '21 at 07:50
  • Does this answer your question? [How to select top 1 and ordered by date in Oracle SQL?](https://stackoverflow.com/questions/44430702/how-to-select-top-1-and-ordered-by-date-in-oracle-sql) – astentx Aug 23 '21 at 07:55
  • 3
    Oracle uses the standard `fetch first 1 rows only` –  Aug 23 '21 at 07:57
  • Does this answer your question? [How do I limit the number of rows returned by an Oracle query after ordering?](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) – Serg Aug 23 '21 at 08:00

1 Answers1

2

You can use ROWNUM:

Select 
     Data-A,
     Data-B,
     (Select * from (
        (
            Select data-C from TableC 
            Where TableC.SourcePtr=Tabbleb.Rkey order by Step Desc
        )
      where rownum = 1 ) as Data-C
From TableA,
     TableB
Where TableA.Source_PTR =TableB.Rkey

or you can use FETCH NEXT N ROWS ONLY:

Select 
     Data-A,
     Data-B,
     (Select DATA-C from TableC 
      Where TableC.SourcePtr=Tabbleb.Rkey order by Step Desc
      FETCH NEXT 1 ROWS ONLY) as Data-C
From TableA,
     TableB
Where TableA.Source_PTR =TableB.Rkey
TimLer
  • 1,320
  • 2
  • 16