0

I have a table M

price1  name             price2
--------------------------------
88      astérix          80
75      cetautomatix     70
95      panoramix        90
20      assurancetourix  30
105     NULL             NULL

And I want to get the max of price2 with corresponding name and price1

So the result I want would be

price1  name             price2
--------------------------------
95      panoramix        90

I know that a lot of pretty similar question were asked here and I achieved to find this solution.

SELECT m.price1, m.nom, m.price2
FROM M m
WHERE m.price2 = (SELECT MAX(m.price2) FROM M m);

However I'd like to find a solution where I need only one request (Just one SELECT, not two)

So I tried things like that 

SELECT m.price1, m.nom, MAX(m.price2)
FROM M m
Group by m.nom, m.price1

But it doesn't work like i want.

So if someone know a way to do it in only one request it would be really helpfull!

Thanks

Peni
  • 626
  • 1
  • 7
  • 18
  • https://stackoverflow.com/questions/tagged/greatest-n-per-group+oracle –  Oct 31 '17 at 14:40

2 Answers2

2

In Oracle, you can do:

select max(price1) keep (dense_rank first order by price2 desc nulls last) as price1,
       max(name) keep (dense_rank first order by price2 desc nulls last) as name,
       max(price2)
from m;

This is not exactly the same as your query. This always returns one row. In the event of ties, the price1 and name might come from different rows.

In Oracle 12C, you could do:

select m.*
from m
order by price2 desc nulls last
fetch first 1 row only;

However, your method with the subquery is perhaps the most efficient way to write the query, if you have an index on m(price2).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

12c Solution:

SELECT M.*
       FROM M
   ORDER BY price2 DESC NULLS LAST
FETCH FIRST 1 ROW ONLY;

For earlier versions, it would be easy if you use multiple selects.

Bikash Ranjan Bhoi
  • 518
  • 2
  • 10