CODEWITHSUNDEEP
sqloracle-database
HC1122
HC1122

Reputation: 414

Get the biggest price using MAX() function

I am using SQL Oracle, and I want to change my query using MAX function and not ROWNUM.

SELECT * 
FROM (SELECT a.name, price.price
FROM price
LEFT JOIN a 
ON a.id = price.tk_a
ORDER BY price.price DESC)
WHERE ROWNUM <=1;

Any help or suggestions please?

Upvotes: 2

Views: 138

Answers (4)

MT0
MT0

Reputation: 167972

You can use the MAX aggregate function with KEEP ( DENSE_RANK FIRST ORDER BY ... ) to get the maximum of another column:

SELECT MAX( a.name ) KEEP ( DENSE_RANK FIRST ORDER BY p.price DESC ) AS name,
       MAX( p.price ) AS price
FROM   a
       LEFT OUTER JOIN price p
       ON ( a.id = p.tk_a );

Upvotes: 2

Matt
Matt

Reputation: 15071

MAX and group by

SELECT a.name, MAX(p.price)
FROM price
LEFT JOIN a ON a.id = p.tk_a
GROUP BY a.name
ORDER BY p.price DESC

If you just want the single MAX price then use this

SELECT MAX(p.price), (SELECT MAX(b.name) FROM a b LEFT JOIN price pp ON b.id = pp.tk_a WHERE a.id = b.id AND p.price = pp.price) AS name
FROM price
LEFT JOIN a ON a.id = p.tk_a
ORDER BY p.price DESC

Upvotes: 1

I A Khan
I A Khan

Reputation: 8839

Use This

SELECT * 
FROM (SELECT a.name,MAX( price.price)
FROM price
LEFT JOIN a 
ON a.id = price.tk_a
GROUP BY a.name 
ORDER BY price.price DESC)

Upvotes: 0

Robert
Robert

Reputation: 25753

Try this:

select a1.name, p1.price
from price p1
JOIN a a1 ON a1.id = p1.tk_a
JOIN (SELECT MAX(p.price)
      FROM price p) C on p1.price = c.price

Upvotes: 1

Related Questions