Select MAX price of a book with JOIN (2 tables) in SQL Server?

Question

I have 2 tables

CREATE TABLE BOOKS 
(
    numbk INT PRIMARY KEY IDENTITY,
    nombk NVARCHAR(60),
    _numrub INT FOREIGN KEY REFERENCES CLASSIFICATION(numrub)
)

CREATE TABLE TARIFER 
(
     _numbk INT FOREIGN KEY REFERENCES BOOKS(numbk),
     _nomed NVARCHAR(60) FOREIGN KEY REFERENCES EDITEURS(nomed),
     _date DATE,
     price DECIMAL(20,2),

     PRIMARY KEY (_numouv, _nomed)
)

The question is: how do I list all titles of books (nombk) that have the max price?

PS: TRAFIER has the price columns, and a foreign key from BOOKS which is _numbk

I tried this:

select 
    o.nombk, max(prix) 
from 
    TARIFER tr, books o 
where 
    o.numbk = tr._numbk
group by
    o.nombk

This lists all, but when I execute this:

select max(prix) 
from TARIFER tr, books o 
where o.numbk = tr._numbk

It returns only the max price. I don't know why. Could someone please explain?

Answer 1

Why not use just a subquery the get the max(prix) and then use that one to list all records with that prix:

select o.nombk ,prix 
from TARIFER tr , books o 
where o.numbk = tr._numbk
  and tr.prix in (select max(prix) from TARIFER tr)