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?

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Oct 28 '19 at 04:49

score 3 · Answer 1 · answered Oct 28 '19 at 00:09

3

In SQL Server, you can use TOP (1) WITH TIES:

select top (1) with ties b.nombk, t.prix
from books b join
     TARIFER t
     on b.numbk = t._numbk
order by t.prix desc;

answered Oct 28 '19 at 00:09

Gordon Linoff

1,242,037
58
646
786

Thanks ! Any other way without using 'with ties . . .' ? – Saad Amrani Oct 28 '19 at 00:20
@0x544D . . . This seems like the most convenient approach. – Gordon Linoff Oct 28 '19 at 00:55

score 1 · Answer 2 · answered Oct 28 '19 at 00:14

1

Both queries to aggregation, but not at the same level:

The first query has group by o.nombk, so it generate one record per book, and gives you the maximum price of this book accross all tarifers.
The second query has no group by clause, hence it gives you the maximum price of all books over all tarifers.

If you want the book with the higher price, there is no need to aggregate: you can join and sort the results by price:

select top (1) with ties b.*, t.*
from books b 
inner join join tarifer t on b.numbk = t._numbk
order by t.prix desc;

top (1) with ties gives you the first record; if there are several records with the same, top price, the query returns them all.

answered Oct 28 '19 at 00:14

GMB

216,147
25
84
135

NICE !! But im not allowed to use with .. Any solution ? – Saad Amrani Oct 28 '19 at 00:18
@0x544D: what are you not allowed to use? – GMB Oct 28 '19 at 00:20
1

The `with` in `with ties` is a special syntax. It does not relate to the keyword `with`, that is used to create *common table expressions*. You can use it. – GMB Oct 28 '19 at 00:22
i meant the with ties indeed They're expecting me to find 2 solutions for that problem , i'm pretty sure they mean by 2 solutions : TOP - max .. – Saad Amrani Oct 28 '19 at 00:24
Why would you want 2 solutions? You could (AFAIK) use `FETCH` with SQL Server... - is this some sort of homework question? – Vérace Oct 28 '19 at 04:07

score 1 · Accepted Answer · answered Oct 28 '19 at 00:33

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)

answered Oct 28 '19 at 00:33

cte6

637
4
8

Thanks i litteraly forgot about old courses of subqueries lol . That will do , Thanks . – Saad Amrani Oct 28 '19 at 00:38

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

3 Answers3