CODEWITHSUNDEEP
sqloracle11g
Adrian
Adrian

Reputation: 17

How to prepare a sql Query with ORDER BY and LIMIT 1 in Oracle 11g?

I need to prepare this query for Oracle 11g database, but it is not compatible with this version:

SELECT * FROM CLIENTS WHERE UPPER(NOMBRE) LIKE ? ORDER BY NUM DESC LIMIT 1;

With ORDER BY NUM DESC LIMIT 1, I only want the first result with the maximum "NUM"

Any alternative to do this in Oracle 11g?

Thanks!

Upvotes: 0

Views: 76

Answers (2)

Pham X. Bach
Pham X. Bach

Reputation: 5432

One way is use rownum pseudo column

SELECT *
FROM
(
    SELECT * 
    FROM clients 
    WHERE UPPER(nombre) LIKE ? 
    ORDER BY num DESC
)
WHERE rownum = 1;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You could use ROW_NUMBER here:

SELECT *
FROM
(
    SELECT c.*, ROW_NUMBER() OVER (ORDER BY NUM DESC) rn
    FROM CLIENTS c
    WHERE UPPER(NOMBRE) LIKE ?
) t
WHERE rn = 1;

Upvotes: 1

Related Questions