Oracle: using WHERE ROWNUM = 1

Question

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

Answer 1

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;  

Answer 2

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.