CODEWITHSUNDEEP
oracle-database
Skulmuk
Skulmuk

Reputation: 605

Oracle: using WHERE ROWNUM = 1

chaps and chapettes

Just a quick question. I need to return only one row from a stored proc., but no matter where I place the WHERE clause, I get errors. Can somebody take a look at the (cut-down due to sheer length) code and let me know where it should go, please?

SELECT        **values** 

INTO          **variables**                  

FROM          **table**

_WHERE        ROWNUM = 1_

INNER JOIN    **other table**
ON            **join target**
ORDER BY      **sort criteria**;

_WHERE        ROWNUM = 1_

Thanks

Upvotes: 4

Views: 50259

Answers (4)

Joel Vega
Joel Vega

Reputation: 61

little bit late, but I got a similar problem and I solved it like this:

SELECT **values** 
INTO **variables** 
FROM **table**
WHERE **condition**
ORDER BY **sort criteria**
FETCH FIRST 1 ROW ONLY;

Regards

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

You were almost correct. You put the WHERE clause after the JOINs, but before the ORDER BY.

SELECT        **values** 

INTO          **variables**                  

FROM          **table**

INNER JOIN    **other table**
ON            **join target**

_WHERE        ROWNUM = 1_

ORDER BY      **sort criteria**;

However, this won't do what you might think - the ORDER BY is evaluated AFTER the where clause; which means this will just pick the first record it finds (that satisfies the join criteria), and will then sort that row (which obviously is a no-op).

The other answers (e.g. IvoTops') give ideas of how to get the first record according to the sort criteria.

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT        **values** 
INTO          **variables**                  
FROM 
    ( SELECT        **values** 
                  , ROW_MUMBER() OVER (ORDER BY  **sort criteria**) AS rn
      FROM          **table**
      INNER JOIN    **other table**
      ON            **join target**  
    ) tmp 
WHERE        rn = 1 ;

Check also this blog post: Oracle: ROW_NUMBER() vs ROWNUM

Upvotes: 1

IvoTops
IvoTops

Reputation: 3531

I believe this is the way to structure rownum queries

SELECT * FROM 
INTO **Variables * *
( SELECT * FROM X 
  WHERE Y 
  ORDER BY Z
) 
WHERE ROWNUM = 1;

Upvotes: 9

Related Questions